﻿
'Description:报工类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Yangsj
'create Date:2017-5-8
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports System.Text
Imports BusinessRule.DTO
Imports DatabaseAccess.DBUtility
Imports DatabaseAccess.IDAL
Imports DatabaseAccess.DALFactory
Imports System.Data.OracleClient
Imports System.Configuration

Public Class uLMSWorkReportBusiness
    Implements IBusiness.uLMSWorkReportIBusiness
    Private m_CheckPoint As New uLMSStepCheckPointBusiness
    Private m_CheckObj As New uLMSCheckBusiness
    Private m_CommonObj As New uLMSCommonBusiness
    Private m_ConCurrentinfo As New uLMSContainercurrentinfoBusiness
    Private usingImg As String = ConfigurationManager.AppSettings("UsingCheckImageSavePath")
#Region "报工块"

    ''' <summary>
    '''  添加生产报工
    ''' Create tianFW
    ''' Modify by Yuxj20190418增加超期原因
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddContainerStepWorkReportInfo(ByVal entity As ContainerStepWorkReportDTO, ByVal value As String) As Boolean Implements IBusiness.uLMSWorkReportIBusiness.AddContainerStepWorkReportInfo
        Dim objPara As Object()
        objPara = FillContainerStepWorkReportInfoTrans(entity, value)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(objPara.First, objPara.Last)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    Public Function FillContainerStepWorkReportInfoTrans(ByVal entity As ContainerStepWorkReportDTO, ByVal value As String) As Object() Implements IBusiness.uLMSWorkReportIBusiness.FillContainerStepWorkReportInfoTrans
        Dim strsql As String =
        <string>
                INSERT INTO CONTAINERSTEPWORKREPORT(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,CHECKERID,QTY,CHECKTYPE,CONTAINERSTEPDISPATCHID,
                CONTAINERSTEPWORKREPORTID,CREATEDATE,ISUSE,SELFCHECKOUTOFTOLERANCEQTY,SELFCHECKLOSSQTY,SELFCHECKQUALIFIEDQTY,ISTRANSFER,Qty2,UOM2ID,SurplusEmployeeID,STEPINFOID,Specimenqty,Specimenlossqty,
                reasontype,reasondesc,firstcheckcontainerno,keyprocessbatchno,notes)
                VALUES(:Containerid,:Factoryid,:Workcenterid,:Teamid,:Workflowstepid,:Resourceid,:Workerid,:Checkerid,:Qty,:Checktype,:Containerstepdispatchid,
                :Containerstepworkreportid,:Createdate,:Isuse,:SELFCHECKOUTOFTOLERANCEQTY,:SELFCHECKLOSSQTY,:SELFCHECKQUALIFIEDQTY,:ISTRANSFER,:Qty2,:UOM2ID,
                :SurplusEmployeeID,:STEPINFOID,:Specimenqty,:Specimenlossqty,:reasontype,:reasondesc,:firstcheckcontainerno,:keyprocessbatchno,:notes)
        </string>
        Dim insertpar(28) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":Containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":Factoryid", entity.FactoryID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":Workcenterid", entity.WorkCenterID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":Teamid", entity.TeamID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":Workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":Resourceid", entity.ResourceID, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":Workerid", entity.WorkerID, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":Checkerid", entity.CheckerID, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":Qty", entity.Qty, OracleType.Number)
        insertpar(9) = DatabaseHelper.NewParameter(":Checktype", entity.CheckType, OracleType.Number)
        insertpar(10) = DatabaseHelper.NewParameter(":Containerstepdispatchid", entity.ContainerStepDispatchID, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":Containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        If entity.CreateDate = DateTime.Parse("0001/1/1") Then
            insertpar(12) = DatabaseHelper.NewParameter(":Createdate", System.DateTime.Now, OracleType.DateTime)
        Else
            insertpar(12) = DatabaseHelper.NewParameter(":Createdate", entity.CreateDate, OracleType.DateTime)
        End If
        insertpar(13) = DatabaseHelper.NewParameter(":Isuse", 0, OracleType.Number)
        '
        insertpar(14) = DatabaseHelper.NewParameter(":SELFCHECKOUTOFTOLERANCEQTY", entity.SelfCheckOutofToleranceQty, OracleType.Number)
        insertpar(15) = DatabaseHelper.NewParameter(":SELFCHECKLOSSQTY", entity.SelfCheckLossQty, OracleType.Number)
        insertpar(16) = DatabaseHelper.NewParameter(":SELFCHECKQUALIFIEDQTY", entity.SeleCheckQualifiedQty, OracleType.Number)
        If value = "split" Then
            insertpar(17) = DatabaseHelper.NewParameter(":ISTRANSFER", 1, OracleType.Number)
        Else
            insertpar(17) = DatabaseHelper.NewParameter(":ISTRANSFER", 0, OracleType.Number)
        End If
        If Not entity.UOM2ID Is Nothing And entity.Qty2 <> 0 Then
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", entity.Qty2, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", entity.UOM2ID, OracleType.VarChar)
        Else
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", DBNull.Value, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.SurplusEmployeeID Is Nothing Then
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", entity.SurplusEmployeeID, OracleType.VarChar)
        Else
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.UOM2ID Is Nothing And entity.Qty2 <> 0 Then
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", entity.Qty2, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", entity.UOM2ID, OracleType.VarChar)
        Else
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", DBNull.Value, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.SurplusEmployeeID Is Nothing Then
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", entity.SurplusEmployeeID, OracleType.VarChar)
        Else
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", DBNull.Value, OracleType.VarChar)
        End If
        If entity.StepInfoID IsNot Nothing Then
            insertpar(21) = DatabaseHelper.NewParameter(":STEPINFOID", entity.StepInfoID, OracleType.VarChar)
        Else
            insertpar(21) = DatabaseHelper.NewParameter(":STEPINFOID", DBNull.Value, OracleType.VarChar)
        End If
        If entity.Specimenqty <> 0 Then
            insertpar(22) = DatabaseHelper.NewParameter(":Specimenqty", entity.Specimenqty, OracleType.Number)
        Else
            insertpar(22) = DatabaseHelper.NewParameter(":Specimenqty", DBNull.Value, OracleType.Number)
        End If
        If entity.Specimenlossqty <> 0 Then
            insertpar(23) = DatabaseHelper.NewParameter(":Specimenlossqty", entity.Specimenlossqty, OracleType.Number)
        Else
            insertpar(23) = DatabaseHelper.NewParameter(":Specimenlossqty", DBNull.Value, OracleType.Number)
        End If

        insertpar(24) = DatabaseHelper.NewParameter("reasontype", IIf(IsNothing(entity.ReasonType), String.Empty, entity.ReasonType), OracleType.VarChar)
        insertpar(25) = DatabaseHelper.NewParameter("reasondesc", IIf(IsNothing(entity.ReasonDesc), String.Empty, entity.ReasonDesc), OracleType.VarChar)
        If entity.FirstContainerNo IsNot Nothing Then
            insertpar(26) = DatabaseHelper.NewParameter(":firstcheckcontainerno", entity.FirstContainerNo, OracleType.VarChar)
        Else
            insertpar(26) = DatabaseHelper.NewParameter(":firstcheckcontainerno", DBNull.Value, OracleType.VarChar)
        End If
        'add zhunan 2022.6.15 报工增加录入关键过程批次号
        If entity.KeyProcessBatchNo IsNot Nothing Then
            insertpar(27) = DatabaseHelper.NewParameter(":keyprocessbatchno", entity.KeyProcessBatchNo, OracleType.VarChar)
        Else
            insertpar(27) = DatabaseHelper.NewParameter(":keyprocessbatchno", DBNull.Value, OracleType.VarChar)
        End If
        'add by zhangrj 2022-07-18 增加备注
        If entity.Note IsNot Nothing Then
            insertpar(28) = DatabaseHelper.NewParameter(":notes", entity.Note, OracleType.VarChar)
        Else
            insertpar(28) = DatabaseHelper.NewParameter(":notes", DBNull.Value, OracleType.VarChar)
        End If
        Return New Object() {strsql, insertpar}

    End Function

    ''' <summary>
    ''' 按唯一ID删除生产报工
    ''' Create tianFW
    ''' </summary>
    ''' <param name="ContainerStepWorkReportID">eg:'1' OR CSW.CONTAINERSTEPWORKREPORTID ='2' OR CSW.CONTAINERSTEPWORKREPORTID ='3'</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepWorkReportInfoBycswrid(ByVal ContainerStepWorkReportID As String) As Boolean Implements IBusiness.uLMSWorkReportIBusiness.DelContainerStepWorkReportInfoBycswrid
        '此处是一条或多条同时置为无效记录
        Dim strsql As String = String.Format("UPDATE CONTAINERSTEPWORKREPORT CSW SET CSW.ISUSE=1,CSW.ISTRANSFER=0 WHERE CSW.CONTAINERSTEPWORKREPORTID = {0}", ContainerStepWorkReportID)
        If (DatabaseHelper.ExecuteSql(strsql) > 0) Then

            Dim tempDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format("select ContainerStepWorkReportID from CONTAINERSTEPWORKREPORT csw WHERE CSW.CONTAINERSTEPWORKREPORTID = {0}",
                                                                                         ContainerStepWorkReportID))

            Dim TranDataBusiness As New uLMSContainercurrentinfoBusiness
            For Each reportRow In tempDt.Rows
                TranDataBusiness.UpdateStepCheckNumWithWorkReportInfo("'" + reportRow("ContainerStepWorkReportID").ToString + "'")

                '清除可能存在的检测项记录 add:Wangjh 20220421
                strsql = "select str.stepcheckpointresultid from stepcheckpointresult str where str.containerstepworkreportid ={0}"
                Dim checkPointDt As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strsql, "'" + reportRow("ContainerStepWorkReportID").ToString + "'"))
                If checkPointDt.Rows.Count > 0 Then
                    For Each checkPonitRow As DataRow In checkPointDt.Rows
                        m_CheckPoint.DeleteWorkerResultById(checkPonitRow("stepcheckpointresultid").ToString, usingImg)
                    Next
                End If
            Next

            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 添加生产报工子序号
    ''' Create tianFw
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddContainerStepWorkReportChildInfo(ByVal entity As ContainerStepWorkReportChildDTO) As Boolean Implements IBusiness.uLMSWorkReportIBusiness.AddContainerStepWorkReportChildInfo
        Dim objPara As Object()
        objPara = FillContainerStepWorkReportChildInfo(entity)
        If (DatabaseHelper.ExecuteSql(objPara.First, objPara.Last) > 0) Then
            Return True
        Else
            Return False
        End If
    End Function
    Public Function FillContainerStepWorkReportChildInfo(ByVal entity As ContainerStepWorkReportChildDTO) As Object() Implements IBusiness.uLMSWorkReportIBusiness.FillContainerStepWorkReportChildInfo
        Dim strsql As String =
            <string>
                INSERT INTO CONTAINERSTEPWORKREPORTCHILD(CONTAINERSTEPWORKREPORTID,CHILDCONTAINERID,SELFCHECKLOSSQTY) VALUES(:Containerstepworkreportid,:Childcontainerid,:Selfchecklossqty)
            </string>
        Dim insertpar(2) As OracleParameter
        'modify 2017/10/10 
        insertpar(0) = DatabaseHelper.NewParameter(":Containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":Childcontainerid", entity.ChildContainerID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":Selfchecklossqty", entity.SelfCheckLossQty, OracleType.VarChar)
        '
        Return New Object() {strsql, insertpar}

    End Function
    ''' <summary>
    ''' 按唯一ID删除生产报工子序号
    ''' Create tianFW
    ''' </summary>
    ''' <param name="ContainerStepWorkReportID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepWorkReportChildInfoByccid(ByVal ContainerStepWorkReportID As String) As Boolean Implements IBusiness.uLMSWorkReportIBusiness.DelContainerStepWorkReportChildInfoByccid
        Dim strsql As String = String.Format("DELETE CONTAINERSTEPWORKREPORTCHILD C WHERE C.CONTAINERSTEPWORKREPORTID = '{0}'", ContainerStepWorkReportID)
        If (DatabaseHelper.ExecuteSql(strsql) > 0) Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 获取报工信息
    ''' Create tianFW
    ''' update:Wangjh 增加 件号/批次号,派工日期查询
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepReportInfo(ByVal para As Dictionary(Of String, String), ByRef sbCondition As StringBuilder) As uMESProcessResult Implements IBusiness.uLMSWorkReportIBusiness.GetStepReportInfo
        Dim sb As New StringBuilder
        Dim strSelect As String = ""
        If para.Keys.Contains("ContainerName") Then
            strSelect =
            <string>
             SELECT NVL(C.WORKFLOWROUTE,C.PRODUCTNAME) PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,
             C.TOGETHERFLAG,C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,C.FACTORYID,(c.productname||'/'||c.containerno) ProductBatch,s.iskey,
            </string>
            sbCondition.AppendLine("FROM CONTAINERCURRENTINFO C")
            sbCondition.AppendLine("INNER JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERID = C.CONTAINERID AND CSD.WORKFLOWSTEPID = C.WORKFLOWSTEPID AND CSD.ISUSE = 0")
            sbCondition.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
            sbCondition.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID")
            sbCondition.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)")
            sbCondition.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID ")
            sbCondition.AppendFormat(" WHERE CSD.ISUSE = 0 AND C.DISPATCHNUM = 1 AND NVL(CSD.ISCOMPLETE,0) = 0 AND C.QTY+NVL(C.ALLLOSSQTY,0)>NVL(C.WORKREPORTNUM,0) AND C.SPLITNUM = 0 AND C.FACTORYID='{0}'", para("FactoryID"))
            If para("ContainerType") = "7" Then
                sbCondition.AppendFormat(" AND C.TOGETHERFLAG = '{0}' AND C.WORKFLOWSTEPID='{1}'", para("ContainerName"), para("WorkflowStepID"))
            Else
                sbCondition.AppendFormat(" AND C.CONTAINERNAME = '{0}' ", para("ContainerName"))
            End If
            sbCondition.AppendFormat(" AND ((CSD.WORKERID IS NOT NULL AND CSD.WORKERID = '{0}') OR (CSD.WORKERID IS NULL AND CSDD.WORKERID='{1}')) ", para("WorkerID"), para("WorkerID")) '单人派工或多人派工
            sbCondition.AppendLine(" AND C.CONTAINERSTATUS=1 AND C.ISINSTORE=0") 'add 2017/11/02 tianFW
            If para("ContainerType") = "7" Then
                sb.AppendLine(strSelect)
                sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,NULL WORKREPORTNUM,NULL ALLLOSSQTY,'' FaContianerID,''BOMID,''CONTAINERSTEPDISPATCHID,''RESOURCEID,'' IsHold,")
                sb.AppendLine("NULL FIRSTCHECKNUM,NULL STEPCHECKNUM,NULL PRIORITYCODE,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,NULL PLANNEDSTARTDATE,NULL PLANNEDFINISHDATE,NULL DispatchDate ")
                sb.AppendLine(sbCondition.ToString)
                sb.AppendLine("AND C.CONTAINERTYPE='7'")
                sb.AppendLine("GROUP BY C.PRODUCTID,C.PRODUCTNAME,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE,C.SPECNO,C.TOGETHERFLAG,C.CONTAINERTYPE,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
                sb.AppendLine("C.MFGORDERNAME,C.PLANQTY,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.WORKFLOWROUTE,c.containerno,s.iskey")
            Else
                sb.AppendLine(strSelect)
                sb.AppendLine("C.QTY AS WIPQTY,C.CONTAINERID,C.CONTAINERNAME,NVL(C.WORKREPORTNUM,0) WORKREPORTNUM,C.ALLLOSSQTY,C.CONTAINERID AS FaContianerID,C.BOMID,CSD.CONTAINERSTEPDISPATCHID,CSD.RESOURCEID,")
                sb.AppendLine("C.FIRSTCHECKNUM,C.STEPCHECKNUM,CSD.PRIORITYCODE,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,CSD.PLANNEDSTARTDATE,CSD.PLANNEDFINISHDATE,trunc(CSD.createdate) DispatchDate,C.IsHold ")
                sb.AppendLine(sbCondition.ToString)
                sb.AppendLine("AND C.CONTAINERTYPE<>'7'")
            End If
        Else
            strSelect =
            <string>
             SELECT NVL(C.WORKFLOWROUTE,C.PRODUCTNAME) PRODUCTNAME,C.PRODUCTID,WS.WORKFLOWSTEPNAME,WS.WORKFLOWSTEPID,S.SPECNO, WS.WORKFLOWID,WS.SEQUENCE,C.TOGETHERFLAG,
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,C.FACTORYID,(c.productname||'/'||c.containerno) ProductBatch,s.iskey,
            </string>
            sb.AppendLine("SELECT * FROM (")
            sb.AppendLine(strSelect)
            sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,'' FaContianerID,''BOMID,''CONTAINERSTEPDISPATCHID,''RESOURCEID,C.WORKFLOWSTEPNAME NOWWORKFLOWSTEPNAME,'' IsHold,")
            sb.AppendLine("C.WORKFLOWSTEPID NOWWORKFLOWSTEPID,NULL FIRSTCHECKNUM,NULL STEPCHECKNUM,NULL PRIORITYCODE,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,NULL PLANNEDSTARTDATE,NULL PLANNEDFINISHDATE,null DispatchDate,NULL WORKREPORTNUM,NULL ALLLOSSQTY ")
            sbCondition.AppendLine("FROM CONTAINERCURRENTINFO C ")
            sbCondition.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERID = C.CONTAINERID AND CSD.ISUSE = 0")
            sbCondition.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
            sbCondition.AppendLine("LEFT JOIN WORKFLOW WF ON WF.WORKFLOWID = WS.WORKFLOWID")
            sbCondition.AppendLine("LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID")
            sbCondition.AppendLine("LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)")
            sbCondition.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID ")
            sbCondition.AppendFormat(" WHERE C.DISPATCHNUM = 1 AND C.SPLITNUM = 0 AND NVL(CSD.ISCOMPLETE,0) = 0 AND C.FACTORYID='{0}'", para("FactoryID"))
            sbCondition.AppendFormat(" AND ((CSD.WORKERID IS NOT NULL AND CSD.WORKERID = '{0}') OR (CSD.WORKERID IS NULL AND CSDD.WORKERID='{1}')) ", para("WorkerID"), para("WorkerID")) '单人派工或多人派工
            sbCondition.AppendLine(" AND C.CONTAINERSTATUS=1 AND C.ISINSTORE=0")
            If para.ContainsKey("CurrentWorkflowStep") Then
                sbCondition.AppendLine(" AND csd.WORKFLOWSTEPID = c.WORKFLOWSTEPID")
                '只能查询当前序时有以下判断，如果直接用C.QTY>NVL(A.REPORTQTY,0)来判断，怎会将有些批次查询不出来，如
                '批次100，报工80并作全部报废，则在制数就变成20，但报工数是80，实际可以再报20，但是C.QTY>NVL(A.REPORTQTY,0)判断就会将该批次过滤掉
                sbCondition.AppendLine(" AND C.QTY+NVL(C.ALLLOSSQTY,0)>NVL(C.WORKREPORTNUM,0)")
            Else
                sbCondition.AppendLine(" AND ws.sequence >= c.workflowstepsequence") 'add 2017/11/02 tianFW
            End If
            sb.AppendLine(sbCondition.ToString)
            sb.AppendLine("AND C.CONTAINERTYPE='7'")
            sb.AppendLine("GROUP BY C.PRODUCTID,C.PRODUCTNAME,WS.WORKFLOWSTEPNAME,WS.WORKFLOWSTEPID,WS.WORKFLOWID,WS.SEQUENCE,S.SPECNO,C.TOGETHERFLAG,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
            sb.AppendLine("C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY,C.FACTORYID,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.WORKFLOWROUTE,c.containerno,s.iskey")
            sb.AppendLine(" UNION ")
            sb.AppendLine(strSelect)
            sb.AppendLine("C.QTY AS WIPQTY ,C.CONTAINERID,C.CONTAINERNAME,C.CONTAINERID AS FaContianerID,C.BOMID,CSD.CONTAINERSTEPDISPATCHID,CSD.RESOURCEID,C.WORKFLOWSTEPNAME NOWWORKFLOWSTEPNAME,C.IsHold,")
            sb.AppendLine("C.WORKFLOWSTEPID NOWWORKFLOWSTEPID,C.FIRSTCHECKNUM,C.STEPCHECKNUM,CSD.PRIORITYCODE,C.PRODUCTDESCRIPTION,C.PRODUCTREVISION,CSD.PLANNEDSTARTDATE,CSD.PLANNEDFINISHDATE,trunc(CSD.createdate) DispatchDate,C.WORKREPORTNUM,C.ALLLOSSQTY ")
            sb.AppendLine(sbCondition.ToString)
            sb.AppendLine("AND C.CONTAINERTYPE<>'7'")
            If para.Keys.Contains("mfgOrderName") Then
                sbCondition.AppendFormat(" AND C.MFGORDERNAME LIKE '%{0}%'", para("mfgOrderName"))
            End If
            sb.AppendLine(") C ORDER BY c.productid,c.containerid,c.sequence")
        End If
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取单件单卡报工明细信息 create by tianFW 2018年11月19日
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepWorkReportDetailInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.GetStepWorkReportDetailInfo
        Dim sb As New StringBuilder
        If para.Keys.Contains("ContainerName") Then
            sb.AppendLine("SELECT C.*,CSD.CONTAINERSTEPDISPATCHID,CSD.RESOURCEID,CSD.PRIORITYCODE,CSD.PLANNEDSTARTDATE,CSD.PLANNEDFINISHDATE,trunc(csd.createdate) DispatchDate")
            sb.AppendLine(para("sbConditon") & " AND ")
        Else
            sb.AppendLine("SELECT * FROM (")
            sb.AppendLine("SELECT C.CONTAINERID,C.CONTAINERNAME,C.PRODUCTNAME,C.PRODUCTID,WS.WORKFLOWSTEPNAME,WS.WORKFLOWSTEPID,S.SPECNO,C.QTY,WS.WORKFLOWID,WS.SEQUENCE, ")
            sb.AppendLine("C.TOGETHERFLAG,REPLACE(C.TOGETHERFLAG,C.PRODUCTNAME||'/','') TOGETHERFLAGNAME,C.WORKFLOWSTEPNAME NOWWORKFLOWSTEPNAME,C.IsHold,C.WORKFLOWSTEPID NOWWORKFLOWSTEPID,")
            sb.AppendLine("C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,")
            sb.AppendLine(" C.FACTORYID,CSD.CONTAINERSTEPDISPATCHID,CSD.RESOURCEID,CSD.PRIORITYCODE,CSD.PLANNEDSTARTDATE,CSD.PLANNEDFINISHDATE,trunc(csd.createdate) DispatchDate ")
            sb.AppendLine(para("sbConditon"))
            sb.AppendLine(" ) C WHERE ")
        End If
        sb.AppendFormat(" C.TOGETHERFLAG='{0}' AND C.PRODUCTID='{1}' AND C.WORKFLOWSTEPID='{2}'", para("togetherFlag"), para("productID"), para("workflowStepID"))
        If para.ContainsKey("nowWorkflowStepID") Then
            sb.AppendFormat(" AND C.NOWWORKFLOWSTEPID='{0}' ", para("nowWorkflowStepID"))
        End If
        If para.ContainsKey("mfgOrderName") Then
            sb.AppendFormat(" AND C.MFGORDERNAME='{0}' ", para("mfgOrderName"))
        End If
        sb.AppendLine(" ORDER BY C.CONTAINERNAME")
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString())
    End Function

    ''' <summary>
    ''' 根据是否 接受任务 进行接受任务数据插入
    ''' create tianFW
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReceviceDispatch(ByVal para As Dictionary(Of String, String)) As Integer Implements IBusiness.uLMSWorkReportIBusiness.ReceviceDispatch
        Dim objpara As Object()
        objpara = FillReceviceDispatch(para)
        ReceviceDispatch = DatabaseHelper.ExecuteSql(objpara.First)
    End Function

    Public Function FillReceviceDispatch(ByVal para As Dictionary(Of String, String)) As Object() Implements IBusiness.uLMSWorkReportIBusiness.FillReceviceDispatch
        Dim strsql As New StringBuilder
        If para("type") = "1" Then
            strsql.AppendLine("UPDATE CONTAINERSTEPDISPATCH C SET C.RECVDATE = SYSDATE,")
            strsql.AppendFormat(" C.RECVEMPLOYEEID ='{0}' WHERE ", para("WorkerID"))
            strsql.AppendFormat(" C.CONTAINERSTEPDISPATCHID IN ({0}) ", para("ContainerStepDispatchIDs"))
            strsql.AppendLine(" AND C.RECVEMPLOYEEID IS NULL")
        ElseIf para("type") = "2" Then
            strsql.AppendFormat(" UPDATE CONTAINERSTEPDISPATCH C SET C.RECVEMPLOYEEID ='{0}'", para("WorkerID"))
            strsql.AppendFormat(" WHERE C.CONTAINERSTEPDISPATCHID ='{0}'", para("ContainerStepDispatchID"))
        End If
        Return New Object() {strsql.ToString, Nothing}
    End Function
    ''' <summary>
    ''' 查询派工单和报工单数据信息
    ''' Create tianFW
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetDispatchInfoToReport(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSWorkReportIBusiness.GetDispatchInfoToReport
        Dim sql As New StringBuilder
        sql.AppendLine("SELECT CSD.CONTAINERSTEPDISPATCHID,CSD.QTY,NVL(A.REPORTQTY,0) AS REPORTQTY,E.FULLNAME ")
        sql.AppendLine("FROM CONTAINERSTEPDISPATCH CSD ")
        sql.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID ")
        sql.AppendLine("LEFT JOIN EMPLOYEE E ON E.EMPLOYEEID = CSD.EMPLOYEEID ")
        sql.AppendLine("LEFT JOIN (SELECT NVL(SUM(CSW.QTY), 0) REPORTQTY,CSW.CONTAINERSTEPDISPATCHID")
        sql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW")
        sql.AppendFormat(" WHERE CSW.CONTAINERSTEPDISPATCHID = '{0}' AND  CSW.ISUSE = 0", para("ContainerStepDispatchID"))
        sql.AppendLine("GROUP BY CSW.CONTAINERSTEPDISPATCHID) A ON A.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        sql.AppendFormat(" WHERE CSD.ISUSE = 0 AND CSD.CONTAINERSTEPDISPATCHID = '{0}'", para("ContainerStepDispatchID"))
        sql.AppendFormat(" AND ((CSD.WORKERID IS NOT NULL AND CSD.WORKERID = '{0}') OR (CSD.WORKERID IS NULL AND CSDD.WORKERID='{1}'))", para("WorkerID"), para("WorkerID"))
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sql.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 获取报工人信息
    ''' Create tianFW
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetWorkerInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSWorkReportIBusiness.GetWorkerInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT distinct CSW.CONTAINERSTEPWORKREPORTID,CSW.CONTAINERID,CSW.WORKFLOWSTEPID,CSW.WORKERID,CSW.CREATEDATE,WC.WORKCENTERNAME,")
        strsql.AppendLine("EM.EMPLOYEENAME,EM.FULLNAME,CSW.QTY,CSW.CREATEDATE,CSWR.CHILDCONTAINERID,C.CONTAINERNAME,NVL(CSW.LOSSQTY,0) LOSSQTY,")
        strsql.AppendLine("NVL(CSW.SELFCHECKQUALIFIEDQTY,0) SELFCHECKQUALIFIEDQTY,NVL(CSW.SELFCHECKOUTOFTOLERANCEQTY,0) SELFCHECKOUTOFTOLERANCEQTY,NVL(CSWR.SELFCHECKLOSSQTY,NVL(CSW.SELFCHECKLOSSQTY,0)) SELFCHECKLOSSQTY")
        strsql.AppendLine(",NVL(CSW.QTY2,0) QTY2,U.UOMNAME,NVL(SpecimenQty,0) SpecimenQty,NVL(SpecimenLossQty,0) SpecimenLossQty,CSW.checktype") 'add:Wangjh 20171219 增加第二数量和单位查询 20181220 增加试件数，需开废票试件数查询
        strsql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW ")
        strsql.AppendLine(" LEFT JOIN UOM U ON U.UOMID=CSW.UOM2ID") 'add:Wangjh 20171219 
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CSW.WORKERID")
        strsql.AppendLine(" LEFT JOIN WORKCENTER WC ON WC.WORKCENTERID = CSW.WORKCENTERID")
        strsql.AppendLine("LEFT JOIN CONTAINERSTEPWORKREPORTCHILD CSWR  ON CSWR.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CSWR.CHILDCONTAINERID ")
        strsql.AppendLine("LEFT JOIN CONTAINERCHECKINFO CC ON CC.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID AND CC.ISUSE = 0")
        strsql.AppendFormat(" WHERE CSW.ISUSE = 0 AND CC.CONTAINERCHECKINFOID IS NULL AND CSW.CONTAINERID IN ({0})", para("ContainerID")) 'update:Wangjh 2017-8-7 增加C.status=1
        strsql.AppendLine(" AND ((CSWR.CONTAINERSTEPWORKREPORTID IS NOT NULL AND C.STATUS=1) OR (CSWR.CONTAINERSTEPWORKREPORTID IS NULL)) ")
        strsql.AppendFormat(" AND CSW.WORKFLOWSTEPID= '{0}'", para("WorkflowStepID"))
        If para.ContainsKey("CheckType") Then
            If para("CheckType") = "2" Then '可能是工序检或者试验件的工序检 add:Wangjh 20180104
                strsql.AppendLine(" AND CSW.CHECKTYPE in (2,3,4,20,25,30,35)") 'add tianFW 2018年3月30日  CSW.CHECKTYPE=4
            Else
                strsql.AppendFormat(" AND CSW.CHECKTYPE='{0}'", para("CheckType"))
            End If
        End If
        If para.ContainsKey("CheckerID") Then
            strsql.AppendFormat(" AND (CSW.CHECKERID='{0}' OR CSW.CHECKERID IS NULL)", para("CheckerID"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
        Return dt
    End Function

    ''' <summary>
    ''' 更新报废信息到报工表 create tianfw
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpDateLossQtyInfo(ByVal para As Dictionary(Of String, String)) As Boolean Implements IBusiness.uLMSWorkReportIBusiness.UpDateLossQtyInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERSTEPWORKREPORT CSW")
        If para.Keys.Contains("Revocation") Then '检验撤销
            strsql.AppendFormat(" SET CSW.LOSSQTY=CSW.LOSSQTY - {0},CSW.CONTAINERFINISHEDCHECKINFOID = NULL", para("LossQty"))
        ElseIf para.Keys.Contains("ContainerStepWorkReportID") And para.Keys.Contains("ContainerFinishedCheckInfoID") Then
            '即存在报工ID又存在成检ID，则就是成检时反写报工记录成检ID和报废数
            strsql.AppendFormat(" SET CSW.LOSSQTY=NVL(CSW.LOSSQTY,0) + {0},CSW.containerfinishedcheckinfoid='{1}'", para("LossQty"), para("ContainerFinishedCheckInfoID"))
        ElseIf para.Keys.Contains("ContainerStepWorkReportID") Then
            strsql.AppendFormat(" SET CSW.LOSSQTY=NVL(CSW.LOSSQTY,0) + {0}", para("LossQty"))
        ElseIf para.Keys.Contains("ContainerFinishedCheckInfoID") Then '此处是单件单卡撤销，如果单件单卡存在成检，则不存在不合格品报废
            strsql.AppendLine(" SET CSW.LOSSQTY = NULL,CSW.ContainerFinishedCheckInfoID = NULL ")
        End If
        Dim strsql_ As New StringBuilder '查询修改批次的查批次ID和工序ID询语句 add 2019年1月24日
        strsql_.AppendLine("SELECT csw.containerid,csw.workflowstepid,null lossqty FROM containerstepworkreport csw ")
        If para.Keys.Contains("ContainerStepWorkReportID") Then
            strsql.AppendFormat(" WHERE CSW.CONTAINERSTEPWORKREPORTID='{0}'", para("ContainerStepWorkReportID"))
            strsql_.AppendFormat(" WHERE CSW.CONTAINERSTEPWORKREPORTID='{0}'", para("ContainerStepWorkReportID"))
        ElseIf para.Keys.Contains("ContainerFinishedCheckInfoID") Then
            strsql.AppendFormat(" WHERE CSW.ContainerFinishedCheckInfoID='{0}'", para("ContainerFinishedCheckInfoID"))
            strsql_.AppendFormat(" WHERE CSW.ContainerFinishedCheckInfoID='{0}'", para("ContainerFinishedCheckInfoID"))
        End If
        Dim dr As DataRow = DatabaseHelper.Query_ReturnDatTable(strsql_.ToString()).Rows(0) '获取报工记录的批次ID和工序ID
        If DatabaseHelper.ExecuteSql(strsql.ToString) > 0 Then
            strsql_.Clear()
            strsql_.AppendLine("SELECT nvl(SUM(csw.lossqty),0) lossqty FROM containerstepworkreport csw")
            strsql_.AppendFormat(" WHERE csw.containerid='{0}' AND csw.workflowstepid='{1}' and csw.isuse=0 ", dr("containerid"), dr("workflowstepid")) '获取批次工序总的报废数
            dr("lossqty") = DatabaseHelper.Query_ReturnDatTable(strsql_.ToString()).Rows(0)("lossqty")
            strsql_.Clear()
            strsql_.AppendFormat("UPDATE containercurrentinfo cu SET cu.alllossqty = {0} WHERE cu.containerid='{1}' AND cu.workflowstepid = '{2}'", dr("lossqty"), dr("containerid"), dr("workflowstepid"))
            DatabaseHelper.ExecuteSql(strsql_.ToString)
            '如果是子序号,需要把子序号还原 add:Wangjh 20220714
            'update:Wanjh 20220927 where ch.containercheckinfoid='{0}' 更改为批次和工序匹配，为了兼容工步级的报废
            If para.ContainsKey("ContainerType") Then
                If para("ContainerType") = "3" Then
                    Dim strSql2 = <string>begin
for x in (select ch.* from checkqtychangerecord ch 
  left join container cc on cc.containerid =ch.containerid--子序号
  where cc.parentcontainerid='{0}' and ch.workflowstepid='{1}' and ch.ScrapSourceType in (1,2) and ch.isuse=0 )
loop
    update container c set c.status=1,c.qty=c.qty+(-x.qty) where c.containerid=x.containerid;
    update Checkqtychangerecord  set isuse=1 WHERE checkqtychangerecordid=x.checkqtychangerecordid;
    update containerstepscrapinfo cp set isuse=1,IsIntegration=1 where qtyhistorydetailsid=x.checkqtychangerecordid;
end loop
commit;
end;</string>.Value
                    'If para.ContainsKey("CheckInfoID") Then
                    '    DatabaseHelper.ExecuteSql(String.Format(strSql2, para("CheckInfoID")))
                    'End If
                    DatabaseHelper.ExecuteSql(String.Format(strSql2, dr("containerid").ToString, dr("workflowstepid").ToString))
                End If
            End If
            Return True
        End If
        Return False
    End Function

    Public Function FillUpdateLossQtyInfo(ByVal para As Dictionary(Of String, String)) As Object() Implements IBusiness.uLMSWorkReportIBusiness.FillUpdateLossQtyInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE CONTAINERSTEPWORKREPORT CSW")
        If para.Keys.Contains("Revocation") Then '检验撤销
            strsql.AppendFormat(" SET CSW.LOSSQTY=CSW.LOSSQTY - {0},CSW.CONTAINERFINISHEDCHECKINFOID = NULL", para("LossQty"))
        ElseIf para.Keys.Contains("ContainerStepWorkReportID") And para.Keys.Contains("ContainerFinishedCheckInfoID") Then
            '即存在报工ID又存在成检ID，则就是成检时反写报工记录成检ID和报废数
            strsql.AppendFormat(" SET CSW.LOSSQTY=NVL(CSW.LOSSQTY,0) + {0},CSW.containerfinishedcheckinfoid='{1}'", para("LossQty"), para("ContainerFinishedCheckInfoID"))
        ElseIf para.Keys.Contains("ContainerStepWorkReportID") Then
            strsql.AppendFormat(" SET CSW.LOSSQTY=NVL(CSW.LOSSQTY,0) + {0}", para("LossQty"))
        ElseIf para.Keys.Contains("ContainerFinishedCheckInfoID") Then '此处是单件单卡撤销，如果单件单卡存在成检，则不存在不合格品报废
            strsql.AppendLine(" SET CSW.LOSSQTY = NULL,CSW.ContainerFinishedCheckInfoID = NULL ")
        End If
        If para.Keys.Contains("ContainerStepWorkReportID") Then
            strsql.AppendFormat(" WHERE CSW.CONTAINERSTEPWORKREPORTID='{0}'", para("ContainerStepWorkReportID"))
        ElseIf para.Keys.Contains("ContainerFinishedCheckInfoID") Then
            strsql.AppendFormat(" WHERE CSW.ContainerFinishedCheckInfoID='{0}'", para("ContainerFinishedCheckInfoID"))
        End If
        Return New Object() {strsql.ToString, Nothing}
        'strsql_.Clear()
        'strsql_.AppendLine("SELECT nvl(SUM(csw.lossqty),0) lossqty FROM containerstepworkreport csw")
        'strsql_.AppendFormat(" WHERE csw.containerid='{0}' AND csw.workflowstepid='{1}'", dr("containerid"), dr("workflowstepid")) '获取批次工序总的报废数
        'dr("lossqty") = DatabaseHelper.Query_ReturnDatTable(strsql_.ToString()).Rows(0)("lossqty")
        'strsql_.Clear()
        'strsql_.AppendFormat("UPDATE containercurrentinfo cu SET cu.alllossqty = {0} WHERE cu.containerid='{1}' AND cu.workflowstepid = '{2}'", dr("lossqty"), dr("containerid"), dr("workflowstepid"))
        'Return strsql_.ToString
        ''DatabaseHelper.ExecuteSql(strsql_.ToString)



    End Function

    ''' <summary>
    ''' 获取员工派工的最大优先级 create tianFW 
    ''' </summary>
    ''' <param name="workerID"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetMaxPriorityCode(ByVal workerID As String) As Int32 Implements IBusiness.uLMSWorkReportIBusiness.GetMaxPriorityCode
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT MAX(CSD.PRIORITYCODE) MAX_PRIORITYCODE")
        strsql.AppendLine("FROM CONTAINERSTEPDISPATCH CSD")
        strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO C ON C.CONTAINERID = CSD.CONTAINERID")
        strsql.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("WHERE(CSD.WORKFLOWSTEPID = C.WORKFLOWSTEPID And C.CONTAINERSTATUS = 1 And CSD.ISUSE = 0)")
        strsql.AppendFormat(" AND ((CSD.WORKERID IS NOT NULL AND CSD.WORKERID = '{0}') OR (CSD.WORKERID IS NULL AND CSDD.WORKERID = '{0}'))", workerID)
        strsql.AppendLine("AND CSD.CONTAINERSTEPDISPATCHID NOT IN(")
        strsql.AppendLine("SELECT CSDD.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("FROM CONTAINERSTEPDISPATCH CSDD")
        strsql.AppendLine("LEFT JOIN CONTAINERCURRENTINFO CO ON CO.CONTAINERID = CSDD.CONTAINERID")
        strsql.AppendLine("LEFT JOIN (SELECT SUM(CSW.QTY) SUMQTY, CSW.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendLine("WHERE CSW.ISUSE = 0 GROUP BY CSW.CONTAINERSTEPDISPATCHID) A")
        strsql.AppendLine("ON A.CONTAINERSTEPDISPATCHID = CSDD.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("WHERE(CO.WORKFLOWSTEPID = CSDD.WORKFLOWSTEPID And (CSDD.QTY <= A.SUMQTY Or CO.QTY + NVL(CO.ALLLOSSQTY, 0) <= NVL(CO.WORKREPORTNUM, 0)))")
        strsql.AppendLine("AND CSDD.ISUSE = 0 AND CO.CONTAINERSTATUS = 1)")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
        If dt.Rows.Count = 0 Then
            Return 1
        Else
            Return Int32.Parse(dt.Rows(0)("MAX_PRIORITYCODE"))
        End If
    End Function

    ''' <summary>
    ''' 获取工厂报工是否选择检验员
    ''' </summary>
    ''' <param name="Factoryid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetUserAttribute_SelectChecker(ByVal Factoryid As String) As String Implements IBusiness.uLMSWorkReportIBusiness.GetUserAttribute_SelectChecker
        Dim strsql As New StringBuilder
        strsql.AppendFormat("SELECT U.USERATTRIBUTENAME, U.ATTRIBUTEVALUE FROM USERATTRIBUTE U WHERE U.PARENTID ='{0}'", Factoryid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
        For Each row As DataRow In dt.Rows
            If row("USERATTRIBUTENAME") = "报工选择检验" Then
                Return row("ATTRIBUTEVALUE")
            End If
        Next
        Return ""
    End Function

    ''' <summary>
    ''' 获取历史报工信息
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetHistoryStepWorkReport(ByVal para As Dictionary(Of String, String)) As DataTable Implements IBusiness.uLMSWorkReportIBusiness.GetHistoryStepWorkReport
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSW.CREATEDATE TXNDATE,DECODE(CSW.CHECKTYPE,1,'首检',3,'试检',4,'返修','其他') CHECKTYPE,EM.FULLNAME,T.TEAMNAME,CSW.CONTAINERSTEPWORKREPORTID,")
        strsql.AppendLine("RE.DESCRIPTION || '(' || RE.RESOURCENAME || ')' RESOURCEENAME,CSW.QTY REPORTQTY,CSW.CONTAINERID,CSW.WORKFLOWSTEPID,CSW.WORKERID,'' CONTAINERNAME,")
        strsql.AppendLine("CCO.CONTAINERCHECKINFOID,CFO.CONTAINERFINISHEDCHECKINFOID,NVL(CSW.LOSSQTY,0) LOSSQTY,CSW.CONTAINERSTEPDISPATCHID")
        strsql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendLine("LEFT JOIN CONTAINERCHECKINFO CCO ON CCO.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID AND CCO.ISUSE = 0")
        strsql.AppendLine("LEFT JOIN CONTAINERFINISHEDCHECKINFO CFO ON CFO.CONTAINERFINISHEDCHECKINFOID = CSW.CONTAINERFINISHEDCHECKINFOID AND CFO.ISUSE = 0")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CSW.WORKERID")
        strsql.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = CSW.TEAMID")
        strsql.AppendLine("LEFT JOIN RESOURCEDEF RE ON RE.RESOURCEID = CSW.RESOURCEID")
        If para.Keys.Contains("ContainerIDs") Then
            strsql.AppendFormat(" WHERE CSW.CONTAINERID IN ({0}) AND CSW.WORKFLOWSTEPID = '{1}' AND CSW.ISUSE=0 ORDER BY CSW.CREATEDATE DESC", para("ContainerIDs"), para("WorkflowStepID"))
        Else
            strsql.AppendFormat(" WHERE CSW.CONTAINERID = '{0}' AND CSW.WORKFLOWSTEPID = '{1}' AND CSW.ISUSE=0 ORDER BY CSW.CREATEDATE DESC", para("ContainerID"), para("WorkflowStepID"))
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString)
    End Function

    ''' <summary>
    ''' 获取撤销报工流水卡信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkReportCancelInfo(ByVal para As Dictionary(Of String, String), ByRef sbCondition_str As String) As uMESProcessResult Implements uLMSWorkReportIBusiness.GetWorkReportCancelInfo
        Dim strSelect As String =
            <string>
             SELECT C.PRODUCTNAME,C.PRODUCTID,C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID,C.SPECNO, C.WORKFLOWID,C.WORKFLOWSTEPSEQUENCE AS SEQUENCE,C.TOGETHERFLAG, 
             C.CONTAINERTYPE,DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,C.MFGORDERNAME,C.PLANQTY,
             C.FACTORYID,(c.productname||'/'||c.containerno) ProductBatch,c.containerno,
            </string>
        Dim sbCondition As New StringBuilder
        Dim sb As New StringBuilder
        sb.AppendLine(strSelect)
        sb.AppendLine("SUM(C.QTY) AS WIPQTY ,'' CONTAINERID,''CONTAINERNAME,'' FaContianerID,'' IsHold")
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendLine("FROM CONTAINERCURRENTINFO CC")
            sbCondition.AppendLine("LEFT JOIN CONTAINERCURRENTINFO C ON CC.CONTAINERID = C.CONTAINERID OR (CC.TOGETHERFLAG = C.TOGETHERFLAG")
            sbCondition.AppendLine("AND CC.PRODUCTID = C.PRODUCTID AND CC.WORKFLOWSTEPID = C.WORKFLOWSTEPID AND C.CONTAINERTYPE = '7')")
            sbCondition.AppendLine("LEFT JOIN (SELECT CSW.CONTAINERID,CSW.WORKFLOWSTEPID,SUM(CSW.QTY) AS NOCHECKREPORTQTY FROM CONTAINERSTEPWORKREPORT CSW")
            sbCondition.AppendLine("LEFT JOIN CONTAINERCHECKINFO CCI ON CCI.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID")
            sbCondition.AppendFormat(" WHERE CSW.ISUSE = 0 AND NVL(CSW.LOSSQTY,0) = 0 AND CCI.CONTAINERCHECKINFOID IS NULL AND CSW.WORKERID='{0}' ", para("WorkerID"))
            sbCondition.AppendLine("AND CSW.CONTAINERFINISHEDCHECKINFOID IS NULL GROUP BY CSW.CONTAINERID,CSW.WORKFLOWSTEPID)")
            sbCondition.AppendLine("A ON  A.CONTAINERID = C.CONTAINERID AND A.WORKFLOWSTEPID = C.WORKFLOWSTEPID")
        Else
            sbCondition.AppendLine("FROM CONTAINERCURRENTINFO C")
            sbCondition.AppendLine("LEFT JOIN (SELECT CSW.CONTAINERID,CSW.WORKFLOWSTEPID,SUM(CSW.QTY) AS NOCHECKREPORTQTY FROM CONTAINERSTEPWORKREPORT CSW")
            sbCondition.AppendLine("LEFT JOIN CONTAINERCHECKINFO CCI ON CCI.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID")
            sbCondition.AppendFormat(" WHERE CSW.ISUSE = 0 AND NVL(CSW.LOSSQTY,0) = 0 AND CCI.CONTAINERCHECKINFOID IS NULL AND CSW.WORKERID='{0}' ", para("WorkerID"))
            sbCondition.AppendLine("AND CSW.CONTAINERFINISHEDCHECKINFOID IS NULL GROUP BY CSW.CONTAINERID,CSW.WORKFLOWSTEPID)")
            sbCondition.AppendLine(" A ON A.CONTAINERID = C.CONTAINERID AND A.WORKFLOWSTEPID = C.WORKFLOWSTEPID")
        End If
        sbCondition.AppendFormat(" WHERE C.DISPATCHNUM = 1 AND C.ISINSTORE=0 AND C.SPLITNUM = 0 AND C.FACTORYID='{0}' AND NVL(C.NONCONFORMINGNUM,0) <> 1", para("FactoryID"))
        If para.Keys.Contains("ContainerName") Then
            sbCondition.AppendFormat(" AND CC.CONTAINERNAME = '{0}'", para("ContainerName"))
        End If
        sbCondition.AppendLine(" AND C.CONTAINERSTATUS=1 AND A.CONTAINERID IS NOT NULL") 'add 2017/11/02 tianFW
        sb.AppendLine(sbCondition.ToString)
        sb.AppendLine("AND C.CONTAINERTYPE='7'")
        sb.AppendLine("GROUP BY C.PRODUCTID, C.PRODUCTNAME, C.WORKFLOWSTEPNAME,C.WORKFLOWSTEPID, C.WORKFLOWID, C.WORKFLOWSTEPSEQUENCE, C.SPECNO,") '将 C.SEQUENCE更换为C.WORKFLOWSTEPSEQUENCE tianFW 2017-06-03
        sb.AppendLine("C.TOGETHERFLAG, C.CONTAINERTYPE,C.MFGORDERNAME,C.PLANQTY,C.FACTORYID,C.WORKFLOWROUTE,c.containerno")
        sb.AppendLine("UNION ")
        sb.AppendLine(strSelect)
        sb.AppendLine("C.QTY AS WIPQTY , C.CONTAINERID,C.CONTAINERNAME,C.CONTAINERID AS FaContianerID,C.IsHold")
        sb.AppendLine(sbCondition.ToString)
        sb.AppendLine("AND C.CONTAINERTYPE<>'7'")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        sbCondition_str = "SELECT C.* " + sbCondition.ToString
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取报工撤销单件单卡批次信息 create by tianFW 2018年11月21日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetCancleWorkReportDetailInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.GetCancleWorkReportDetailInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine(para("sbCondition"))
        strsql.AppendFormat(" AND C.MFGORDERNAME = '{0}' ", para("mfgOrderName"))
        strsql.AppendFormat(" AND C.TOGETHERFLAG = '{0}' ", para("toGetherFlag"))
        strsql.AppendFormat(" AND C.PRODUCTID = '{0}' ", para("productID"))
        strsql.AppendFormat(" AND C.WORKFLOWSTEPID = '{0}' ", para("workflowStepID"))
        strsql.AppendLine("AND C.CONTAINERTYPE = '7'")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 根据选择的批次获取已经报工未检验的报工记录 create tianFW 2017年12月6日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetNoCheckWorkReportInfo(ByVal para As Dictionary(Of String, String)) Implements uLMSWorkReportIBusiness.GetNoCheckWorkReportInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSW.CONTAINERSTEPWORKREPORTID,CSW.CREATEDATE REPORTDATE,CSW.QTY REPORTQTY,DECODE(CSW.CHECKTYPE,1,'首件',2,'其他',3,'试件',4,'返件',20,'自动',25,'下料报') CHECKTYPE")
        strsql.AppendLine(",EM.FULLNAME WORKERNAME,C.CONTAINERNAME,CSW.CONTAINERID,csw.specimenqty,csw.specimenlossqty,csw.containerstepdispatchid")
        If para.ContainsKey("GetChecker") Then
            strsql.AppendLine(",EM2.FULLNAME CHECKERNAME")
        End If
        strsql.AppendLine("FROM  CONTAINERSTEPWORKREPORT CSW ")
        strsql.AppendLine("LEFT JOIN EMPLOYEE EM ON EM.EMPLOYEEID = CSW.WORKERID")
        strsql.AppendLine("LEFT JOIN CONTAINER C ON C.CONTAINERID = CSW.CONTAINERID")
        If para.ContainsKey("GetChecker") Then
            strsql.AppendLine("LEFT JOIN EMPLOYEE EM2 ON EM2.EMPLOYEEID = CSW.checkerid")
        End If
        strsql.AppendLine("LEFT JOIN CONTAINERCHECKINFO CCI ON CCI.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID AND CCI.ISUSE=0")
        strsql.AppendFormat(" WHERE CCI.CONTAINERCHECKINFOID IS NULL AND CSW.ISUSE = 0 AND ({0}) AND CSW.WORKFLOWSTEPID='{1}' AND CSW.WORKERID='{2}'", para("ContainerIDs"), para("WorkflowStepID"), para("WorkerID"))
        strsql.AppendLine(" AND CSW.CONTAINERFINISHEDCHECKINFOID IS NULL ORDER BY CSW.CREATEDATE DESC,CSW.CONTAINERID")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    'yuxj20221121 总体报工方法更新，简化sql查询
    ''' <summary>
    ''' 总体报工查询sql
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetZSWorkReportSql(ByVal para As Dictionary(Of String, String)) As String
        Dim strsql As String =
            <string>SELECT ep.bigsmallbomid bomid,ep.mfgordername,ep.mfgorderid,ep.workflowid,ep.concontainerid containerid,ep.machineno machinename,ep.containerno,S.SHIFTNAME, T.TEAMNAME,
                    ep.containername,ep.workflowstepname,ep.productid,ep.WORKFLOWSTEPNAME MACHINESTEPNAME, ep.WORKFLOWSTEPID MACHINESTEPID, EP.PLANNEDSTARTDATE PLANNEDSTARTDATE,
                           EP.PLANNEDFINISHDATE PLANNEDCOMPLETIONDATE,ep.productdesc,ep.WORKFLOWNAME || ':' || ep.workflowver WORKFLOWNAMEVER, ep.workflowdesc WFDESC, EP.ISROUTINETEST
                    FROM ENGINEPARTPLAN ep
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        strsql =
            <string>
                    INNER JOIN (SELECT CSD.CONTAINERID, CSDD.WORKERID
                    FROM CONTAINERSTEPDISPATCHDETAIL_ZS CSDD
                    LEFT JOIN CONTAINERSTEPDISPATCH CSD  ON CSD.CONTAINERSTEPDISPATCHID =
                                                                    CSDD.CONTAINERSTEPDISPATCHID
                    LEFT JOIN CONTAINERSTEPWORKREPORT CSW  ON CSW.CONTAINERSTEPDISPATCHID =CSD.CONTAINERSTEPDISPATCHID AND CSW.ISUSE = 0                                                    
               
                    WHERE {2} AND CSD.FACTORYID = '{0}' AND CSD.ISUSE = 0              
                    AND CSDD.WORKERID = '{1}' GROUP BY CSD.CONTAINERID, CSDD.WORKERID) A ON a.containerid =EP.CONCONTAINERID        
                </string>
        strsql = String.Format(strsql, para("FactoryID"), para("WorkerID"), IIf(para.Keys.Contains("notCheck"), "CSW.CONTAINERSTEPWORKREPORTID IS NOT NULL AND csw.CheckerID IS NULL", "CSW.CONTAINERSTEPWORKREPORTID IS NULL")) 'modify by longb 2024-04-24
        sb.AppendLine(strsql)
        sb.AppendLine("LEFT JOIN TEAMEMPLOYEE TE ON TE.EMPLOYEEID = A.WORKERID ")
        sb.AppendFormat("LEFT JOIN TEAM T ON T.TEAMID = TE.TEAMID ")
        sb.AppendFormat("LEFT JOIN SHIFT S ON S.SHIFTID = T.SHIFTID ")
        sb.AppendLine(" WHERE ep.usecompleted='0'")
        If para.Keys.Contains("ContainerNo") Then
            sb.AppendFormat(" AND ep.ContainerNo like '%{0}%' ", para("ContainerNo"))
        End If

        If para.ContainsKey("MachineStart") Then
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat(" AND ep.machineno between  '{0}' and '{1}' ", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat(" AND ep.machineno like '%{0}%' ", para("MachineStart"))
            End If
        End If

        If para.ContainsKey("MachineStatus") Then
            sb.AppendFormat(" AND ep.workflowstepid='{0}' ", para("MachineStatus"))
        End If
        'add by longb 2024-04-24
        If para.ContainsKey("mfgordername") Then
            sb.AppendFormat(" AND ep.mfgordername like '%{0}%' ", para("mfgordername"))
        End If

        If para.ContainsKey("BeginDate") Then
            If para.ContainsKey("EndDate") Then
                'Modify by Yuxj20180611更改时间列
                sb.AppendFormat("and ep.plannedstartdate >=to_date('{0}','YYYY/MM/DD') and  ep.plannedfinishdate <= to_date('{1}','YYYY/MM/DD') ",
                                    para("BeginDate"), para("EndDate"))
            Else
                sb.AppendFormat("and ep.PLANNEDSTARTDATE >= to_date('{0}','YYYY/MM/DD') ", para("BeginDate"))
            End If
        End If
        sb.AppendLine("and ep.createdate >= sysdate-465 ")
        Return sb.ToString
    End Function

    ''' <summary>
    ''' 获取装试员工报工批次信息
    ''' 增加订单ep.mfgorderid
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZSWorkReportContainerInfo(ByVal para As Dictionary(Of String, String), Optional ByVal isZs As Boolean = False) As DataTable Implements uLMSWorkReportIBusiness.GetZSWorkReportContainerInfo
        Dim strsql As String = GetZSWorkReportSql(para)
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function


    ''' <summary>
    ''' 获取员工没有报工的批次工艺下的工序
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkerNotReportWorkflowstepInfo(ByVal para As Dictionary(Of String, String), Optional ByVal isZS As Boolean = False) As DataTable Implements uLMSWorkReportIBusiness.GetWorkerNotReportWorkflowstepInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSD.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME,st.spectypename")
        strsql.AppendLine(" ,WS.SEQUENCE,s.OPDesc,S.SPECNO,DECODE(WS.IsLastStep,1,'是','否') IsLastStep,DECODE(s.IsKey,1,'是','否') IsKey,T.TEAMNAME") 'add by longb 2022-05-31
        strsql.AppendLine(" ,nvl(apd.starttime,MIN(CSD.PLANNEDSTARTDATE)) PLANNEDSTARTDATE,nvl(apd.endtime,MAX(CSD.PLANNEDFINISHDATE)) PLANNEDFINISHDATE") 'add by longb(2022 - 5 - 31)
        strsql.AppendLine(" ,DECODE(CSD.RecvEmployeeID,NULL,'未接收',DECODE(CSD.STARTWORKEMPID,NULL,'已接收','装配中')) STATUS,DECODE(itr.ISSUEERPMATERIALID,NULL,'未发送',decode(itr.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成','已发送')) SendSTATUS") 'add by longb 2022-05-31
        strsql.AppendLine(" FROM CONTAINERSTEPDISPATCH CSD")
        strsql.AppendLine("LEFT JOIN CONTAINERSTEPWORKREPORT CSW ON CSW.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID AND CSW.ISUSE=0")
        If isZS Then
            strsql.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCHDETAIL_ZS CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        Else
            strsql.AppendLine("LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID")
        End If

        strsql.AppendLine("LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID")
        strsql.AppendLine(<string>LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
        LEFT JOIN spec s ON s.specid=NVL(sb.revofrcdid,ws.specid)
        LEFT JOIN spectype st ON st.spectypeid=s.spectypeid</string>.Value)
        strsql.AppendLine("LEFT JOIN TEAM T ON T.TEAMID = WS.TEAMID") 'add by longb 2022-05-31
        'strsql.AppendLine("LEFT JOIN issueerpmaterial im ON im.WORKFLOWSTEPID=CSD.WORKFLOWSTEPID") 'add by longb 2022-05-31
        'strsql.AppendLine("LEFT JOIN IssueMaterialTransport itr ON itr.ISSUEERPMATERIALID =im.ISSUEERPMATERIALID") 'add by longb 2022-05-31
        strsql.AppendLine(<string>
                           LEFT JOIN (SELECT e.CONTAINERID,iss.* FROM IssueMaterialTransport iss 
                                LEFT JOIN ENGINEPARTPLAN e ON e.ENGINEPARTPLANID =iss.ENGINEPARTPLANID WHERE iss.APPLYTYPE='1' ) itr ON itr.CONTAINERID =CSD.CONTAINERID AND itr.WORKFLOWSTEPID=CSD.WORKFLOWSTEPID
                           LEFT JOIN apsplandetail apd ON apd.containerid=csd.containerid AND apd.workflowstepid=csd.workflowstepid                   
                    </string>)
        strsql.AppendLine("WHERE CSW.CONTAINERSTEPWORKREPORTID IS NULL AND CSD.ISUSE = 0 ")
        strsql.AppendFormat(" AND ((CSD.WORKERID IS NULL AND CSDD.WORKERID='{0}') OR (CSD.WORKERID IS NOT NULL AND CSD.WORKERID='{0}')) ", para("WorkerID")) '
        strsql.AppendFormat(" AND WS.WORKFLOWID='{0}' AND CSD.CONTAINERID='{1}'", para("WorkflowID"), para("ContainerID"))
        strsql.AppendFormat(" GROUP BY CSD.WORKFLOWSTEPID,WS.WORKFLOWSTEPNAME,WS.SEQUENCE,s.OPDesc,S.SPECNO,st.spectypename,WS.IsLastStep,IsKey,T.TEAMNAME,CSD.RecvEmployeeID,CSD.STARTWORKEMPID,apd.starttime,apd.endtime,DECODE(itr.ISSUEERPMATERIALID,NULL,'未发送',decode(itr.STATUS,0,'待接收',5,'已接收',10,'配送中',15,'配送完',20,'收料完成','已发送'))  ORDER BY WS.SEQUENCE") 'add by longb 2022-05-31
        'strsql.AppendLine("SELECT * FROM () WHERE  to_char(PLANNEDFINISHDATE,'yyyy-MM-dd')<=to_char(SYSDATE,'yyyy-MM-dd')") 'modify by longb 2022-05-31
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取上一序的报工信息 
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetLastWorkflowStepWorkReportInfo(ByVal containerid As String) As DataTable Implements uLMSWorkReportIBusiness.GetLastWorkflowStepWorkReportInfo
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSW.WORKFLOWSTEPID FROM CONTAINERSTEPWORKREPORT CSW") '上一个报工记录（有效的）的工序
        strsql.AppendFormat("WHERE CSW.ISUSE = 0 AND CSW.CONTAINERID = '{0}'", containerid)
        strsql.AppendLine("ORDER BY CSW.CREATEDATE DESC")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
        If dt.Rows.Count = 0 Then
            Return New DataTable
        End If
        strsql.Clear()
        strsql.AppendLine("SELECT NVL(A.REPORTQTY,0) REPORTQTY,NVL(A.LOSSQTY,0) LOSSQTY FROM(")
        strsql.AppendLine("SELECT SUM(CSW.QTY) REPORTQTY,SUM(CSW.LOSSQTY) LOSSQTY") '测试检验一个序只有一个报工记录（按多个来处理）
        strsql.AppendLine("FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendLine("WHERE CSW.ISUSE = 0 AND CSW.CHECKTYPE = 3")
        strsql.AppendFormat(" AND CSW.CONTAINERID = '{0}' AND CSW.WORKFLOWSTEPID = '{1}') A", containerid, dt.Rows(0)("WORKFLOWSTEPID"))
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 获取批次当前序是否存在测试件检验(type=3)
    ''' </summary>
    ''' <param name="containerid"></param>
    ''' <param name="workflowstepid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerCurrentIsReportType_3(ByVal containerid As String, ByVal workflowstepid As String) As Integer Implements uLMSWorkReportIBusiness.GetContainerCurrentIsReportType_3
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT CSW.CONTAINERID FROM CONTAINERSTEPWORKREPORT CSW")
        strsql.AppendLine("WHERE CSW.ISUSE=0 AND CSW.CHECKTYPE=3")
        strsql.AppendFormat(" AND CSW.CONTAINERID='{0}' AND CSW.WORKFLOWSTEPID='{1}'", containerid, workflowstepid)
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString()).Rows.Count
    End Function

    ''' <summary>
    ''' 通过派工记录获取点击报工时的派工记录对应的报工数量 create by tianFW 2018年5月24日
    ''' </summary>
    ''' <param name="ContainerStepDispatchInfoIDs">派工IDs</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkReportTimeReallyDisReportNum(ByVal ContainerStepDispatchInfoIDs As String) As DataTable Implements uLMSWorkReportIBusiness.GetWorkReportTimeReallyDisReportNum
        'update:Wangjh 20220511 此查询太慢，注释重写
        'Dim strsql As String =
        '    <string>
        '        SELECT csd.qty,nvl(cw.reportqty,0) reportqty FROM containerstepdispatch csd
        '        left join(
        '     SELECT SUM(csw.qty) reportqty, csw.containerstepdispatchid
        '     FROM containerstepworkreport csw
        '     WHERE csw.isuse = 0
        '     GROUP BY csw.containerstepdispatchid
        '     ) cw on csd.containerstepdispatchid = cw.containerstepdispatchid
        '        WHERE csd.isuse = 0 AND 
        '    </string>
        'Dim sq As New StringBuilder
        'sq.AppendFormat("{0}{1}", strsql, ContainerStepDispatchInfoIDs)
        'Return DatabaseHelper.Query_ReturnDatTable(sq.ToString())

        Dim strSql = <String>SELECT csd.containerstepdispatchid,csd.qty,sum(nvl(cw.qty,0)) reportqty FROM containerstepdispatch csd
LEFT JOIN containerstepworkreport cw on csd.containerstepdispatchid = cw.containerstepdispatchid AND cw.isuse=0
WHERE  1=1 </String>.Value
        strSql += String.Format(" and ({0})", ContainerStepDispatchInfoIDs)
        strSql += " and csd.isuse=0 "
        strSql += " GROUP BY csd.qty,csd.containerstepdispatchid "
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 添加工人设备状态信息 create by tianFW 2018年7月19日
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function AddWorkResourceStatusInfo(ByVal dt As DataTable) As Boolean Implements uLMSWorkReportIBusiness.AddWorkResourceStatusInfo
        AddWorkResourceStatusInfo = False
        'Dim SQLlist As New Dictionary(Of String, OracleParameter())()
        Dim strSQL As String
        Dim oraclepara(2) As OracleParameter
        strSQL = "INSERT INTO WorkerResourcesStatusInfo(Workerid,Resourceid,Resourcestatus,Createdate) VALUES(:Workerid,:Resourceid,:Resourcestatus,SYSDATE)"
        For Each dr As DataRow In dt.Rows
            Array.Clear(oraclepara, 0, oraclepara.Length)
            oraclepara(0) = DatabaseHelper.NewParameter(":Workerid", dr("Workerid"), OracleType.VarChar)
            oraclepara(1) = DatabaseHelper.NewParameter(":Resourceid", dr("Resourceid"), OracleType.VarChar)
            oraclepara(2) = DatabaseHelper.NewParameter(":Resourcestatus", dr("Resourcestatus"), OracleType.VarChar)
            'SQLlist.Add(strSQL, oraclepara)
            DatabaseHelper.ExecuteSql(strSQL, oraclepara)
        Next
        'DatabaseHelper.ExecuteSqlTranByHash(SQLlist)
        AddWorkResourceStatusInfo = True
    End Function

    ''' <summary>
    ''' 获取批次工序试验件报工 create by tianFW 2018年12月19日
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function ExistSJWorkReport(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.ExistSJWorkReport
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT * FROM containerstepworkreport csw ")
        strsql.AppendFormat(" WHERE csw.isuse = 0 AND csw.workflowstepid='{0}'", para("WorkflowStepID"))
        strsql.AppendFormat(" AND csw.containerid IN ('{0}') ", para("ContainerIDs"))
        If Not para.ContainsKey("isExistWorkReportRecord") Then
            strsql.AppendLine("AND NVL(csw.specimenqty,0)>0")
        End If
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 更新批次首检或工序检状态标识 add bytianFW 2019年1月3日
    ''' </summary>
    ''' <param name="containerIDs">批次ID</param>
    ''' <param name="Type">1 首检 2 工序检</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStepCheckNum(ByVal containerIDs As String, ByVal Type As Integer) As Boolean Implements uLMSWorkReportIBusiness.UpdateStepCheckNum
        UpdateStepCheckNum = False
        Dim objpara As Object()
        objpara = FillUpdateStepCheckNum(containerIDs, Type)
        If DatabaseHelper.ExecuteSql(objpara.First) > 0 Then
            UpdateStepCheckNum = True
        End If
    End Function

    Public Function FillUpdateStepCheckNum(ByVal containerIDs As String, ByVal Type As Integer) As Object() Implements uLMSWorkReportIBusiness.FillUpdateStepCheckNum
        Dim strsql As New StringBuilder
        strsql.AppendLine("UPDATE containercurrentinfo cu SET")
        If Type = 1 Then
            strsql.AppendLine("cu.firstchecknum = 1")
        Else
            strsql.AppendLine("cu.stepchecknum = 1")
        End If
        strsql.AppendFormat(" WHERE cu.containerid in ('{0}')", containerIDs)
        Return New Object() {strsql.ToString, Nothing}

    End Function
    ''' <summary>
    ''' 更新批次当前序的报工总数
    ''' </summary>
    ''' <param name="containerIDs">批次IDs</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateWorkReportNum(ByVal containerIDs As String) As Boolean Implements uLMSWorkReportIBusiness.UpdateWorkReportNum
        UpdateWorkReportNum = False
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT nvl(sum(csw.qty),0) reportqty,csw.containerid")
        strsql.AppendLine("FROM containerstepworkreport csw")
        strsql.AppendLine("INNER JOIN containercurrentinfo cu ON cu.containerid = csw.containerid AND cu.workflowstepid = csw.workflowstepid")
        strsql.AppendFormat(" WHERE csw.containerid IN ('{0}') AND csw.isuse = 0 GROUP BY csw.containerid", containerIDs)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
        Dim dic As New Dictionary(Of String, OracleParameter())
        Dim IDs As String() = containerIDs.Replace("','", ",").Split(",")
        Dim isUpdate As Boolean = False
        For Each id As String In IDs '需要处理所有变化批次，如果查出来的记录为空，则就是批次当前序报工记录被删除了。需要直接置为0
            isUpdate = False
            For Each dr As DataRow In dt.Rows
                If dr("containerid") = id Then
                    dic.Add(String.Format("UPDATE containercurrentinfo cu SET cu.workreportnum = {0} WHERE cu.containerid='{1}'", dr("reportqty"), dr("containerid")), Nothing)
                    isUpdate = True
                    Exit For
                End If
            Next
            If Not isUpdate Then
                dic.Add(String.Format("UPDATE containercurrentinfo cu SET cu.workreportnum = 0 WHERE cu.containerid='{0}'", id), Nothing)
            End If
        Next
        DatabaseHelper.ExecuteSqlTranByHash(dic)
        UpdateWorkReportNum = True
    End Function

    ''' <summary>
    ''' 更新工人可能录入的
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateStepCheckPointReportId(ByVal dto As ContainerStepWorkReportDTO) As String Implements uLMSWorkReportIBusiness.UpdateStepCheckPointReportId
        Dim strSql = "begin update stepcheckpointresult sc set sc.containerstepworkreportid='{0}' where sc.containerid='{1}' and sc.workerid='{2}' and sc.containerstepworkreportid is null and sc.stepcheckpointid in "
        strSql += " (select st.stepcheckpointid from stepcheckpoint st where st.workflowstepid='{3}' "
        strSql = String.Format(strSql, dto.ContainerStepWorkReportID, dto.ContainerID, dto.WorkerID, dto.WorkflowstepID)
        If Not String.IsNullOrWhiteSpace(dto.StepInfoID) Then
            strSql += " and st.stepinfoid='" + dto.StepInfoID + "'"
        End If
        strSql += " );"

        '三坐标情况录入的结果值,更新add:Wangjh 20230529
        strSql += <string> 
update stepcheckpointresult sc set sc.containerstepworkreportid='{0}' where sc.containerid='{1}' and sc.containerstepworkreportid is null
and sc.stepcheckpointid in (
select sd.stepcheckpointid from sendthreecoordinaterecord sd 
left join stepcheckpoint st on st.stepcheckpointid=sd.stepcheckpointid
where sd.isuse=0  and sd.issend=1 
and sd.containerid='{1}' and sd.workflowstepid='{2}'</string>.Value
        strSql = String.Format(strSql, dto.ContainerStepWorkReportID, dto.ContainerID, dto.WorkflowstepID)
        If Not String.IsNullOrWhiteSpace(dto.StepInfoID) Then
            strSql += " and st.stepinfoid='" + dto.StepInfoID + "'"
        End If
        strSql += " );"
        strSql += " end;"

        Return strSql
    End Function

#End Region

#Region "装配块"
    ''' <summary>
    ''' 获取派工的工序
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoList(ByVal para As System.Collections.Generic.Dictionary(Of String, String), Optional ByVal isZs As Boolean = False) As System.Data.DataTable Implements IBusiness.uLMSWorkReportIBusiness.GetStepInfoList
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT wfs.workflowstepid,wfs.workflowstepname,so.stepinfoid, so.Description stepinfodes,sob.stepinfoname,st.spectypename FROM workflowstep wfs  ")
            .AppendLine("LEFT JOIN STEPINFO SO ON wfs.workflowstepid=so.workflowstepid ")
            .AppendLine("LEFT JOIN  STEPINFOBASE SOB ON SO.STEPINFOID = SOB.REVOFRCDID  ")
            .AppendLine("LEFT JOIN SPECTYPE ST ON ST.SPECTYPEID = SO.SPECTYPEID ")
            .AppendLine("left join (")
            .AppendLine(" SELECT ds.containerid,ds.workflowstepid,csdd.workerid,ds.stepinfoid FROM CONTAINERSTEPDISPATCH ds ") 'add Yangjiang 20180605 ds.stepinfoid
            If isZs = True Then
                .AppendLine("  LEFT JOIN CONTAINERSTEPDISPATCHDETAIL_ZS CSDD ON csdd.containerstepdispatchid=ds.containerstepdispatchid ")
            Else
                .AppendLine("  LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON csdd.containerstepdispatchid=ds.containerstepdispatchid ")
            End If

            .AppendLine(" where ds.isuse = 0 ") '将 1= 1 修改为 ds.isuse = 0 tianFW 2018年9月29日

            If para.ContainsKey("ContainerID") Then
                .AppendFormat(" and ds.containerid='{0}'", para("ContainerID"))
            End If

            If para.ContainsKey("WorkerID") Then
                .AppendFormat(" and csdd.WorkerID='{0}'", para("WorkerID"))
            End If

            'add YangJiang 20180605 装配记录筛选派工信息
            If para.ContainsKey("StepInfoIDs") Then
                .AppendFormat(" and ds.stepinfoid IN ({0})", para("StepInfoIDs"))
            End If
            'add Yangjiang 20180605 ds.stepinfoid AND so.stepinfoid=ds.stepinfoid
            .AppendLine("group by ds.containerid,ds.workflowstepid,csdd.workerid,ds.stepinfoid ) ds ON ds.workflowstepid=wfs.workflowstepid ")
            If para.ContainsKey("StepInfoIDs") Then
                .AppendLine(" AND so.stepinfoid=ds.stepinfoid ")
            End If

            .AppendLine(" where 1=1 ")
            If para.ContainsKey("WorkflowID") Then
                .AppendFormat(" and wfs.WorkflowID='{0}'", para("WorkflowID"))
            End If

            If para.ContainsKey("ContainerID") Then
                .AppendFormat(" and ds.containerid='{0}'", para("ContainerID"))
            End If
        End With
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString())
    End Function
    ''' <summary>
    ''' 获取任务单
    ''' </summary>
    ''' <param name="cid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetTogetherFlag(ByVal cid As String) As String Implements uLMSWorkReportIBusiness.GetTogetherFlag
        Dim strsql As String = String.Format("select togetherflag from container c where c.containerid='{0}'", cid)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count > 0 Then
            Return dt.Rows(0)("togetherflag").ToString
        Else
            Return ""
        End If
    End Function

    ''' <summary>
    ''' 获取任务单确认数量
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetIssueConfirmData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.GetIssueConfirmData
        Dim m_ContainerObj As New uLMSContainerBusiness
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2） add by longb 2024-03-12
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim sb As New StringBuilder
        With sb
            .AppendLine(" SELECT im.containerno,im.issueerpmaterialid,cm.productname,cm.productbatchno,{0} firstassembleqty,imc.confirmqty")
            .AppendLine("FROM (SELECT SUM(confirmqty) confirmqty,issueerpmaterialid FROM issueerpmaterialconfirmation GROUP BY issueerpmaterialid) imc ")
            .AppendLine("LEFT JOIN issueerpmaterial im ON imc.issueerpmaterialid=im.issueerpmaterialid ")
            .AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid ")
            .AppendLine("LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid ")
            'Modify by Yuxj20180702增加工步筛选
            .AppendLine("  LEFT JOIN productmateriallistitem PL ON pm.productid=pl.Productid ")
            .AppendLine(<string>LEFT JOIN ( SELECT WFS.WORKFLOWSTEPID,s.SPECID FROM WORKFLOWSTEP WFS
                     LEFT JOIN Workflow wf ON wfs.workflowid = wf.workflowid
                     LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid
                     LEFT JOIN spec s ON s.specid = NVL(sb_.revofrcdid,wfs.specid)) ws ON ws.SPECID =pm.SPECID </string>) 'add by longb 2022-08-22
            .AppendLine("where imc.confirmqty>0 ")
            'Modify by Yuxj20180702增加显示工步对应的大小件,不为消耗件
            .AppendLine(" and  pl.Bigsmallpart IS NOT NULL ")
            If para.ContainsKey("ContainerID") Then
                .AppendFormat(" and im.containerid='{0}'", para("ContainerID"))
            End If

            If para.ContainsKey("ContainerNo") Then
                .AppendFormat(" and im.containerno='{0}'", para("ContainerNo"))
            End If

            '添加工步by Yuxj20180702
            If para.ContainsKey("StepInfoId") Then
                .AppendFormat(" and PL.Stepinfoid='{0}'", para("StepInfoId"))
            End If
            'add by longb 2022-08-22
            If para.ContainsKey("workflowstepid") Then
                .AppendFormat(" AND ws.WORKFLOWSTEPID='{0}'", para("workflowstepid"))
            End If

            .AppendLine("group by im.containerno,im.issueerpmaterialid,cm.productname,cm.productbatchno,{0},imc.confirmqty ")
        End With
        Return DatabaseHelper.Query_ReturnDatTable(String.Format(sb.ToString(), qty))
    End Function


    ' ''' <summary>
    ' ''' 获取任务单确认数量
    ' ''' </summary>
    ' ''' <param name="para"></param>
    ' ''' <returns></returns>
    ' ''' <remarks></remarks>
    'Private Function GetIssueConfirmData(ByVal para As Dictionary(Of String, String)) As DataTable
    '    Dim strsql As String =
    '        <string>
    '                SELECT im.containerno,
    '                       im.issueerpmaterialid,
    '                       cm.productname,
    '                       cm.productbatchno,
    '                       pm.firstassembleqty,
    '                       imc.confirmqty
    '                  FROM (SELECT SUM(confirmqty) confirmqty, issueerpmaterialid
    '                          FROM issueerpmaterialconfirmation
    '                         GROUP BY issueerpmaterialid) imc
    '                  LEFT JOIN issueerpmaterial im ON imc.issueerpmaterialid =
    '                                                   im.issueerpmaterialid
    '                  LEFT JOIN partmenuinfo pm ON im.partmenuinfoid = pm.partmenuinfoid
    '                  LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid =
    '                                                     cm.collecterpmaterialid
    '                  LEFT JOIN productmateriallistitem PL ON pm.productid = pl.Productid
    '                  LEFT JOIN (SELECT WFS.WORKFLOWSTEPID, s.SPECID
    '                               FROM WORKFLOWSTEP WFS                                   
    '                               LEFT JOIN specbase sb_ ON sb_.specbaseid = wfs.specbaseid
    '                               LEFT JOIN spec s ON s.specid =
    '                                                   NVL(sb_.revofrcdid, wfs.specid)) ws ON ws.SPECID =
    '                                                                                          pm.SPECID
    '                 where imc.confirmqty > 0 and pl.bomid='{0}'
    '            </string>
    '    strsql = String.Format(strsql, para("BomId"))
    '    Dim sb As New StringBuilder
    '    With sb
    '        .AppendLine(strsql)
    '        If para.ContainsKey("ContainerID") Then
    '            .AppendFormat(" and im.containerid='{0}'", para("ContainerID"))
    '        End If

    '        If para.ContainsKey("ContainerNo") Then
    '            .AppendFormat(" and im.containerno='{0}'", para("ContainerNo"))
    '        End If

    '        '添加工步by Yuxj20180702
    '        If para.ContainsKey("StepInfoId") Then
    '            .AppendFormat(" and PL.Stepinfoid='{0}'", para("StepInfoId"))
    '        End If
    '        'add by longb 2022-08-22
    '        If para.ContainsKey("workflowstepid") Then
    '            .AppendFormat(" AND ws.WORKFLOWSTEPID='{0}'", para("workflowstepid"))
    '        End If

    '        .AppendLine("group by im.containerno,im.issueerpmaterialid,cm.productname,cm.productbatchno,pm.firstassembleqty,imc.confirmqty ")
    '    End With
    '    Return DatabaseHelper.Query_ReturnDatTable(sb.ToString())
    'End Function

    ''' <summary>
    '''  添加装配记录
    ''' Create Yuxj
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddWorkReportMaterialRecord(ByVal entity As ContainerStepWorkReportDTO) As Boolean Implements IBusiness.uLMSWorkReportIBusiness.AddWorkReportMaterialRecord
        Dim strsql As String = ""
        strsql =
        <string>
           INSERT INTO workreportmaterialrecord
              (containerid, createdate, employeeid, guidedoc, issueerpmaterialid, issuereason, issuetype, isuse, note,  
                qty,stepinfoid, workflowstepid, workreportmaterialrecordid,checktype)
            VALUES
              (:containerid, sysdate, :employeeid, :guidedoc, :issueerpmaterialid, :issuereason, :issuetype, :isuse, :note,
                 :qty, :stepinfoid, :workflowstepid, :workreportmaterialrecordid,:checktype)
            </string>

        Dim insertpar(12) As OracleParameter

        insertpar(0) = DatabaseHelper.NewParameter(":Containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":employeeid", entity.WorkerID, OracleType.VarChar)
        If entity.GuideDoc = "" Then 'modify by tianFW 传""空字符串则没有取值 2018年5月28日
            insertpar(2) = DatabaseHelper.NewParameter(":guidedoc", DBNull.Value, OracleType.VarChar)
        Else
            insertpar(2) = DatabaseHelper.NewParameter(":guidedoc", entity.GuideDoc, OracleType.VarChar)
        End If
        insertpar(3) = DatabaseHelper.NewParameter(":issueerpmaterialid", entity.IssueERPMaterialID, OracleType.VarChar)
        If entity.IssueReason = -1 Then 'modify by tianFW 传-1则没有取值 2018年5月28日
            insertpar(4) = DatabaseHelper.NewParameter(":issuereason", DBNull.Value, OracleType.Number)
        Else
            insertpar(4) = DatabaseHelper.NewParameter(":issuereason", entity.IssueReason, OracleType.Number)
        End If
        If entity.IssueType = -1 Then 'modify by tianFW 传-1则没有取值 2018年5月28日
            insertpar(5) = DatabaseHelper.NewParameter(":issuetype", DBNull.Value, OracleType.Number)
        Else
            insertpar(5) = DatabaseHelper.NewParameter(":issuetype", entity.IssueType, OracleType.Number)
        End If
        insertpar(6) = DatabaseHelper.NewParameter(":isuse", entity.IsUse, OracleType.Number)
        insertpar(7) = DatabaseHelper.NewParameter(":Qty", entity.Qty, OracleType.Number)
        insertpar(8) = DatabaseHelper.NewParameter(":stepinfoid", entity.StepInfoID, OracleType.VarChar)
        insertpar(9) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":workreportmaterialrecordid", entity.WorkReportMaterialRecordID, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":note", entity.Note, OracleType.VarChar)
        insertpar(12) = DatabaseHelper.NewParameter(":checktype", entity.CheckType, OracleType.VarChar)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 获取装配记录
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetWorkreportmaterialrecord(ByVal para As Dictionary(Of String, String)) As DataSet Implements uLMSWorkReportIBusiness.GetWorkreportmaterialrecord
        Dim m_ContainerObj As New uLMSContainerBusiness
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("containerid")) '判断是初装（1）、检装（2） add by longb 2024-03-12
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If
        Dim ds As New DataSet
        Dim strsql As String =
            <string>
                 SELECT  wm.createdate,e.employeename,wm.guidedoc,wm.issueerpmaterialid,wm.issuereason,
                        wm.issuereason,DECODE(wm.issuereason,0,'正常装配',1,'反复装配',2,'报废换新件','其他') issuereasondesc,
                        wm.issuetype,DECODE(wm.issuetype,0,'正常件',1,'选配件',2,'替换件',3,'周转件',4,'串件','') issuetypedesc,
                        wm.workflowstepid,wm.stepinfoid,wm.workreportmaterialrecordid ,wm.qty,
                        cm.productname,cm.productbatchno,{0} firstassembleqty,e.employeename,
                        sob.stepinfoname,wfs.workflowstepname,si.Description stepinfodes
                        FROM Workreportmaterialrecord wm
                        LEFT JOIN workflowstep wfs ON wm.workflowstepid=wfs.workflowstepid
                        LEFT JOIN stepinfo SI ON to_char(si.Stepinfoid)=to_char(wm.Stepinfoid)--modify by longb 20220902
                        LEFT JOIN stepinfobase sob ON si.Stepinfobaseid=sob.stepinfobaseid                        
                        LEFT JOIN employee e ON wm.employeeid=e.employeeid
                        LEFT JOIN issueerpmaterial im ON wm.issueerpmaterialid=im.issueerpmaterialid
                        LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
                        LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid
                        WHERE wm.isuse=0 
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(String.Format(strsql, qty))
        If para.ContainsKey("stepinfoid") Then
            sb.AppendFormat(" and wm.stepinfoid='{0}' ", para("stepinfoid"))
        End If

        If para.ContainsKey("workflowstepid") Then
            sb.AppendFormat(" AND wm.workflowstepid ='{0}' ", para("workflowstepid"))
        End If

        If para.ContainsKey("productname") Then
            sb.AppendFormat("AND cm.productname='{0}' ", para("productname"))
        End If

        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
        Dim dv As DataView = dt.DefaultView
        Dim dtCollect As DataTable = dv.ToTable(True, "issueerpmaterialid", "productname", "firstassembleqty", "workflowstepname", "stepinfoname", "stepinfodes")
        dtCollect.Columns.Add("issueQty")
        For Each dr As DataRow In dtCollect.Rows
            dr("issueQty") = dt.Compute("sum(qty)",
                                   String.Format("issueerpmaterialid='{0}' ", dr("issueerpmaterialid")))
        Next

        Dim dtChild As DataTable = dv.ToTable(True, "WorkflowStepID", "StepInfoID", "workreportmaterialrecordid", "productname", "issueerpmaterialid", "productbatchno", "qty", "employeename", "createdate", "issuetype", "issuetypedesc", "issuereasondesc", "issuereason", "guidedoc")

        dtCollect.TableName = "Collect"
        dtChild.TableName = "Child"

        ds.Tables.Add(dtCollect)
        ds.Tables.Add(dtChild)

        ds.Relations.Add(dtCollect.Columns("issueerpmaterialid"), dtChild.Columns("issueerpmaterialid"))
        Return ds
    End Function

    ''' <summary>
    ''' 删除装配记录
    ''' </summary>
    ''' <param name="strid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function DelWorkreportmaterialrecord(ByVal strid As String) As Boolean Implements uLMSWorkReportIBusiness.DelWorkreportmaterialrecord
        DelWorkreportmaterialrecord = False
        Dim strsql = String.Format(" delete from Workreportmaterialrecord where Workreportmaterialrecordid='{0}' ", strid)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 获取已经装配数量
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetHasIssueQty(ByVal para As Dictionary(Of String, String)) As String Implements uLMSWorkReportIBusiness.GetHasIssueQty
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT nvl(SUM(w.qty),0) qty FROM Workreportmaterialrecord w ")
        sb.AppendFormat(" WHERE w.issueerpmaterialid='{0}' and w.workflowstepid='{1}'",
                        para("issueerpmaterialid"), para("workflowstepid")) ' and w.stepinfoid='{2}' , para("stepinfoid")
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString())
        Return dt.Rows(0)("qty").ToString
    End Function

    Private Function GetProductInfobyCid(ByVal cid As String) As DataTable Implements uLMSWorkReportIBusiness.GetProductInfobyCid
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT p.productid, p.productrevision,p.description,pb.productname,c.containertype,")
        sb.AppendLine("DECODE(C.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,")
        sb.AppendLine(" c.containername,c.containerid,c.togetherflag,m.mfgordername FROM container c ") 'Modify by Yuxj20180705,c.togetherflag
        sb.AppendLine("LEFT JOIN product p ON c.productid=p.productid ")
        sb.AppendLine("LEFT JOIN productbase pb ON p.productbaseid=pb.productbaseid ")
        sb.AppendLine("LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid") 'add YangJiang 20180719 查询订单名称
        sb.AppendFormat("WHERE c.containerid='{0}'", cid)
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function
    ''' <summary>
    ''' 更新装配--件号为完工状态
    ''' add by longb 2022-08-22
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateWorkreportmaterialrecord(ByVal entity As ContainerStepWorkReportDTO) As Boolean Implements uLMSWorkReportIBusiness.UpdateWorkreportmaterialrecord
        Dim strSql As New StringBuilder
        strSql.AppendFormat(" UPDATE WorkReportMaterialRecord SET ISFINISH='{0}' where CONTAINERID='{1}' AND ISSUEERPMATERIALID='{2}' AND WORKFLOWSTEPID='{3}'", entity.IsFinish, entity.ContainerID, entity.IssueERPMaterialID, entity.WorkflowstepID)
        Return DatabaseHelper.ExecuteSql(strSql.ToString) > 0
    End Function
    ''' <summary>
    ''' 查询-装配记录--已装完
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetWorkreportmaterialrecordStatus(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.GetWorkreportmaterialrecordStatus
        Dim strSql As New StringBuilder
        strSql.AppendFormat("SELECT ISSUEERPMATERIALID,ISFINISH FROM WorkReportMaterialRecord WHERE CONTAINERID='{0}' AND WORKFLOWSTEPID='{1}'", para("ContainerID"), para("StepID"))
        strSql.AppendLine(<string>UNION ALL SELECT ISSUEERPMATERIALID,CASE WHEN QTY>=CONFIRMQTY THEN '1' ELSE '' END ISFINISH FROM (
                            SELECT a.ISSUEERPMATERIALID,SUM(a.QTY) QTY,i.CONFIRMQTY FROM WorkReportMaterialRecord a 
                            LEFT JOIN (SELECT SUM(CONFIRMQTY) CONFIRMQTY,ISSUEERPMATERIALID FROM  ISSUEERPMATERIALCONFIRMATION GROUP BY ISSUEERPMATERIALID) i ON i.ISSUEERPMATERIALID =a.ISSUEERPMATERIALID</string>)
        strSql.AppendFormat("WHERE a.CONTAINERID='{0}' AND a.WORKFLOWSTEPID='{1}'", para("ContainerID"), para("StepID"))
        strSql.AppendLine("GROUP BY a.ISSUEERPMATERIALID,i.CONFIRMQTY)")
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 获取总体某本工艺的标准工时yuxj20230414
    ''' </summary>
    ''' <param name="workflowid"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetZSWorkflowWorkTime(ByVal workflowid) As DataTable Implements uLMSWorkReportIBusiness.GetZSWorkflowWorkTime
        Dim strsql As String =
            <string>
                select nvl(sf.durationperunit,0) durationperunit,nvl(sf.durationperunit2,0) durationperunit2,
                       ws.workflowstepid,sf.stepinfoid
                from workflowstep ws
                left join stepinfo sf on ws.workflowstepid=sf.workflowstepid
                where ws.workflowid='{0}'
            </string>
        Return DatabaseHelper.Query_ReturnDatTable(String.Format(strsql, workflowid))
    End Function
#End Region

#Region "分解记录"
    Function GetWRSplitmaterialrecord(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSWorkReportIBusiness.GetWRSplitmaterialrecord
        Dim m_ContainerObj As New uLMSContainerBusiness
        Dim strFirstSecondIssue As String = m_ContainerObj.CheckFirstSecondIssueNew(para("ContainerID")) '判断是初装（1）、检装（2） add by longb 2024-03-12
        Dim qty As String = "pm.firstassembleqty"
        If strFirstSecondIssue = "2" Then
            qty = "pm.checkassembleqty "
        End If

        Dim sb As New StringBuilder
        Dim strsql As String =
            <string>                
                SELECT WM.CREATEDATE,
                       WM.GUIDEDOC,
                       WM.ISSUEERPMATERIALID,
                       WM.ISSUEREASON,                       
                       e.employeename,
                       DECODE(WM.ISSUEREASON,
                              0,
                              '正常装配',
                              1,
                              '反复装配',
                              2,
                              '报废换新件',
                              '其他') ISSUEREASONDESC,
                       WM.ISSUETYPE,
                       DECODE(WM.ISSUETYPE,
                              0,
                              '正常件',
                              1,
                              '选配件',
                              2,
                              '替换件',
                              3,
                              '周转件',
                              4,
                              '串件',
                              '') ISSUETYPEDESC,
                       WM.WORKFLOWSTEPID,
                       WM.STEPINFOID,
                       WM.WORKREPORTMATERIALRECORDID, 
                       CM.COLLECTERPMATERIALID,                     
                       CM.PRODUCTNAME,
                       CM.PRODUCTBATCHNO,
                       {0} FIRSTASSEMBLEQTY,
                       --SOB.STEPINFONAME,SI.description stepinfodes,
                       WFS.WORKFLOWSTEPNAME,wr.totalqty,wm.qty-nvl(wr.totalqty,0) qty
                  FROM WORKREPORTMATERIALRECORD WM                  
                  LEFT JOIN employee e ON wm.employeeid=e.employeeid
                  LEFT JOIN WORKFLOWSTEP WFS
                    ON WM.WORKFLOWSTEPID = WFS.WORKFLOWSTEPID
                  --LEFT JOIN stepinfo SI ON si.Workflowstepid=wfs.workflowstepid
                  --LEFT JOIN stepinfobase sob ON si.Stepinfobaseid=sob.stepinfobaseid                  
                  LEFT JOIN ISSUEERPMATERIAL IM 
                    ON WM.ISSUEERPMATERIALID = IM.ISSUEERPMATERIALID
                  LEFT JOIN COLLECTERPMATERIAL CM
                    ON IM.COLLECTERPMATERIALID = CM.COLLECTERPMATERIALID
                  LEFT JOIN PARTMENUINFO PM
                    ON IM.PARTMENUINFOID = PM.PARTMENUINFOID                            
            </string>
        sb.AppendLine(strsql)
        sb.AppendLine(" LEFT JOIN (SELECT wr.workreportmaterialrecordid,sum(wr.qty) totalqty from WREPORTSPLITMATERIALRECORD WR ")
        If para.ContainsKey("ContainerID") Then
            sb.AppendFormat(" where wr.containerid='{0}' ", para("ContainerID"))
        End If
        sb.AppendLine("GROUP BY wr.workreportmaterialrecordid) wr ")
        sb.AppendLine("ON WM.WORKREPORTMATERIALRECORDID = WR.WORKREPORTMATERIALRECORDID  ")
        sb.AppendLine(" where isuse=0")
        If para.ContainsKey("ContainerID") Then
            sb.AppendFormat(" and wm.containerid='{0}' ", para("ContainerID"))
        End If
        sb.AppendLine(" and wm.qty-nvl(wr.totalqty,0)>0")

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = String.Format(sb.ToString, qty)
        GetWRSplitmaterialrecord = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    '''  添加分解记录
    ''' Create Yuxj
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddSplitReportMaterialRecord(ByVal entity As ContainerStepWorkReportDTO) As Boolean Implements IBusiness.uLMSWorkReportIBusiness.AddSplitReportMaterialRecord
        Dim strsql As String = ""
        strsql =
        <string>
           INSERT INTO WReportSplitMaterialRecord
              (containerid, createdate, employeeid, guidedoc, issueerpmaterialid, issuereason, isuse,   
                qty,stepinfoid, workflowstepid, workreportmaterialrecordid,WReportSplitMaterialRecordid)
            VALUES
              (:containerid, sysdate, :employeeid, :guidedoc, :issueerpmaterialid, :issuereason, :isuse, 
                 :qty, :stepinfoid, :workflowstepid, :workreportmaterialrecordid,:WReportSplitMaterialRecordid)
            </string>

        Dim insertpar(10) As OracleParameter

        insertpar(0) = DatabaseHelper.NewParameter(":Containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":employeeid", entity.WorkerID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":guidedoc", entity.GuideDoc, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":issueerpmaterialid", entity.IssueERPMaterialID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":issuereason", entity.IssueReason, OracleType.Number)
        insertpar(5) = DatabaseHelper.NewParameter(":isuse", entity.IsUse, OracleType.Number)
        insertpar(6) = DatabaseHelper.NewParameter(":Qty", entity.Qty, OracleType.Number)
        insertpar(7) = DatabaseHelper.NewParameter(":stepinfoid", entity.StepInfoID, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(9) = DatabaseHelper.NewParameter(":workreportmaterialrecordid", entity.WorkReportMaterialRecordID, OracleType.VarChar)
        'insertpar(11) = DatabaseHelper.NewParameter(":note", entity.Note, OracleType.VarChar)
        insertpar(10) = DatabaseHelper.NewParameter(":WReportSplitMaterialRecordid", entity.SplitReportMaterialRecordID, OracleType.VarChar)

        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function
#End Region

#Region "大修机分解后入库申请，入库及出库"
    ''' <summary>
    ''' 获取所有分解后的记录
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetContainerSplitData(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSWorkReportIBusiness.GetContainerSplitData
        Dim sb As New StringBuilder
        sb.AppendLine("SELECT wr.containerid,c.containername,c.togetherflag, pb.productname,p.description ")
        'sb.AppendLine("NVL2(sp.preposerdate,NVL2(sp.instoreemployeedate,NVL2(sp.senddate,'已出库','待出库'),'待入库'),'待申请') flag ")
        sb.AppendLine(" FROM WReportSplitMaterialRecord wr ")
        sb.AppendLine(" LEFT JOIN SplitMaterialInStoreRecord sp ON wr.wreportsplitmaterialrecordid=sp.wreportsplitmaterialrecordid ")
        sb.AppendLine(" LEFT JOIN container c ON wr.containerid=c.containerid ")
        sb.AppendLine(" LEFT JOIN product p ON p.productid=c.productid ")
        sb.AppendLine(" LEFT JOIN productbase pb ON pb.productbaseid=p.productbaseid ")
        'Modify by Yuxj20180702修改厂别取值来源
        sb.AppendLine(" LEFT JOIN mfgorder m ON m.mfgorderid=c.mfgorderid ")
        sb.AppendLine(" LEFT JOIN Factory f ON m.factoryid=f.factoryid ")
        sb.AppendLine(" LEFT JOIN Userattribute u ON u.parentid=m.mfgorderid  AND u.userattributename='发动机' ")
        sb.AppendLine(" where c.status='1' ")

        If para.ContainsKey("Type") Then
            If para("Type") = "0" Then
                sb.AppendLine(" AND sp.proposerid IS NULL ")
            End If

            If para("Type") = "1" Then
                sb.AppendLine(" AND sp.proposerid IS not NULL and sp.instoreemployeeid is null ")
            End If

            If para("Type") = "2" Then
                sb.AppendLine(" And sp.instoreemployeeid is not null and sp.senddate is null ")
            End If
        End If

        If para.ContainsKey("ContainerNo") Then
            sb.AppendFormat("and c.togetherflag='{0}' ", para("ContainerNo"))
        End If

        If para.ContainsKey("FactoryId") Then
            sb.AppendFormat("and f.FactoryId='{0}' ", para("FactoryId"))
        End If

        If para.ContainsKey("ProductName") Then
            sb.AppendFormat("and pb.ProductName='{0}' ", para("ProductName"))
        End If

        If para.ContainsKey("MachineStart") Then
            If para.ContainsKey("MachineEnd") Then
                sb.AppendFormat("and u.attributevalue between  '{0}' and '{1}' ", para("MachineStart"), para("MachineEnd"))
            Else
                sb.AppendFormat("and u.attributevalue='{0}' ", para("MachineStart"))
            End If
        End If
        sb.AppendLine("Group by wr.containerid,c.containername,c.togetherflag, pb.productname,p.description  ")
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex").ToString)
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = sb.ToString
        GetContainerSplitData = DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取分解记录表各记录的入库情况
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function GetSplitProductBatchnoData(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.GetSplitProductBatchnoData
        Dim strsql As String =
            <string>
                SELECT cm.productbatchno,cm.productname,pm.firstassembleqty,im.issueqty,wr.qty splitQty,wr.wreportsplitmaterialrecordid,wfs.workflowstepname,sob.stepinfoname,
                       NVL2(sp.preposerdate,NVL2(sp.instoreemployeedate,NVL2(sp.senddate,'已出库','待出库'),'待入库'),'待申请') status
                 FROM WReportSplitMaterialRecord wr
                 LEFT JOIN SplitMaterialInStoreRecord sp ON wr.wreportsplitmaterialrecordid=sp.wreportsplitmaterialrecordid
                 LEFT JOIN issueerpmaterial im ON wr.issueerpmaterialid=im.issueerpmaterialid
                 LEFT JOIN partmenuinfo pm ON im.partmenuinfoid=pm.partmenuinfoid
                 LEFT JOIN Collecterpmaterial cm ON im.collecterpmaterialid=cm.collecterpmaterialid
                 LEFT JOIN workflowstep wfs ON wr.workflowstepid=wfs.workflowstepid
                 LEFT JOIN stepinfo so ON wr.stepinfoid=so.stepinfoid
                 LEFT JOIN stepinfobase sob ON so.stepinfoid=sob.revofrcdid
                 
            </string>
        Dim sb As New StringBuilder
        sb.AppendLine(strsql)
        sb.AppendLine("WHERE wr.isuse='0' ")
        If para.Keys.Contains("ContainerId") Then
            sb.AppendFormat(" and wr.containerid='{0}'", para("ContainerId"))
        End If

        If para.ContainsKey("Type") Then

            If para("Type") = "0" Then
                sb.AppendLine(" AND sp.proposerid IS NULL ")
            End If

            If para("Type") = "1" Then
                sb.AppendLine(" AND sp.proposerid IS not NULL and sp.instoreemployeeid is null ")
            End If

            If para("Type") = "2" Then
                sb.AppendLine(" And sp.instoreemployeeid is not null and sp.senddate is null ")
            End If
        End If

        sb.AppendLine(" order by cm.productname,cm.productbatchno,status asc ")

        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString)
    End Function

    Function AddSplitMaterialInStoreRecord(ByVal entity As ContainerStepWorkReportDTO) As Boolean Implements uLMSWorkReportIBusiness.AddSplitMaterialInStoreRecord
        Dim strsql As String =
            <string>
                INSERT INTO splitmaterialinstorerecord(isuse, preposerdate, proposerid, wreportsplitmaterialrecordid)
                values(0,sysdate,:proposerid,:wreportsplitmaterialrecordid)
            </string>
        Dim insertpar(1) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":wreportsplitmaterialrecordid", entity.SplitReportMaterialRecordID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":proposerid", entity.WorkerID, OracleType.VarChar)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(strsql, insertpar)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function

    ''' <summary>
    ''' 更新大修分解入库表，flag 0:入库申请，1：入库，2：出库
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <param name="flag"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function UpdateSplitMaterialInStoreRecord(ByVal entity As ContainerStepWorkReportDTO, ByVal flag As String) As Boolean Implements uLMSWorkReportIBusiness.UpdateSplitMaterialInStoreRecord
        Dim sb As New StringBuilder
        sb.AppendLine(" update splitmaterialinstorerecord set ")
        If flag = "1" Then
            sb.AppendFormat(" InStoreEmployeeID='{0}',InStoreEmployeeDate=sysdate ", entity.WorkerID)
        ElseIf flag = "2" Then
            sb.AppendFormat(" Sender='{0}',SendDate=sysdate ", entity.WorkerID)
        End If
        sb.AppendFormat("where wreportsplitmaterialrecordid in ('{0}') ", entity.SplitReportMaterialRecordID)
        Dim rows As Integer = DatabaseHelper.ExecuteSql(sb.ToString)
        If (rows = 0) Then
            Return False
        Else
            Return True
        End If
    End Function
#End Region

#Region "装试"
    ''' <summary>
    ''' 获取装配整合信息 create tianFW 2018年5月9日
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetIssueMaterialIntegratedData(ByVal para As Dictionary(Of String, String)) Implements uLMSWorkReportIBusiness.GetIssueMaterialIntegratedData
        Dim sb As New StringBuilder
        With sb
            .AppendLine("SELECT im.issueerpmaterialid,cm.collecterpmaterialid,pm.partmenuinfoid,nvl(im.issueqty,0) issueqty,")
            .AppendLine("cm.productname, cm.productbatchno,b.confirmqty,im.createdate,nvl(a.lossqty,0) lossqty")
            .AppendLine(",CASE WHEN eb.stepinfocount IS NULL THEN '未创建' WHEN eb.stepinfocount = 0 THEN '已创建'")
            .AppendLine(" WHEN eb.stepinfocount > 0 THEN '已完成' END status")
            .AppendLine(" ,CASE WHEN d.issueerpmaterialid IS NULL AND dd.issueerpmaterialid IS NULL THEN '未排故' ELSE '已排故' END DealStatus") 'add YangJiang 20180719
            .AppendLine("FROM issueerpmaterial im")
            .AppendLine("LEFT JOIN (SELECT SUM(imsi.lossqty) lossqty, imsi.issueerpmaterialid")
            .AppendLine("           FROM issueerpmaterialscrapinfo imsi GROUP BY imsi.issueerpmaterialid) a")
            .AppendLine("ON a.issueerpmaterialid = im.issueerpmaterialid")
            .AppendLine("LEFT JOIN (SELECT SUM(imc.confirmqty) confirmqty, imc.issueerpmaterialid")
            .AppendLine("           FROM issueerpmaterialconfirmation imc GROUP BY imc.issueerpmaterialid) b")
            .AppendLine("ON b.issueerpmaterialid = im.issueerpmaterialid")
            .AppendLine("LEFT JOIN partmenuinfo pm ON im.partmenuinfoid = pm.partmenuinfoid")
            .AppendLine("LEFT JOIN collecterpmaterial cm ON im.collecterpmaterialid = cm.collecterpmaterialid")
            .AppendLine("LEFT JOIN (SELECT SUM(DECODE(ex.stepinfoid, NULL, 0,1)) stepinfocount,ex.comcontainername,")
            .AppendLine("ex.containerno,ex.productid FROM exchangebill ex GROUP BY ex.comcontainername, ex.containerno, ex.productid) eb")
            .AppendFormat("on eb.containerno = im.containerno and eb.comcontainername = cm.productbatchno and eb.productid='{0}'", para("ProductID"))
            'add YangJiang 20180719判断是否排故
            .AppendLine("LEFT JOIN dealfaultbill d ON d.issueerpmaterialid=im.issueerpmaterialid")
            .AppendLine("LEFT JOIN dealfaultbilldetail dd ON dd.issueerpmaterialid=im.issueerpmaterialid")

            .AppendLine("where b.confirmqty>0 ")
            If para.ContainsKey("ContainerID") Then
                .AppendFormat(" and im.containerid='{0}'", para("ContainerID"))
            End If
            If para.ContainsKey("ProductName") Then
                .AppendFormat(" and cm.productname='{0}'", para("ProductName"))
            End If
        End With
        Return DatabaseHelper.Query_ReturnDatTable(sb.ToString())
    End Function

    ''' <summary>
    ''' 装试大阶段报工 create tianFW 2018年5月4日
    ''' 增加finishdate的记录 Modify by Yuxj20181016
    ''' </summary>
    ''' <param name="enginepartplanID" >分解计划唯一ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function BigStageWorkReport(ByVal enginepartplanID As String) As Boolean Implements uLMSWorkReportIBusiness.BigStageWorkReport
        Dim strsql As String = String.Format("UPDATE enginepartplan ep SET ep.usecompleted = 1,ep.finishdate=sysdate WHERE ep.enginepartplanid='{0}'", enginepartplanID)
        If DatabaseHelper.ExecuteSql(strsql) > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    ''' <summary>
    ''' 判断本大阶段的前面大阶段是否完工
    ''' </summary>
    ''' <param name="mfgid"></param>
    ''' <param name="strSequence"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function CheckPreStepComplete(ByVal mfgid As String, ByVal strSequence As String) As DataTable Implements uLMSWorkReportIBusiness.CheckPreStepComplete
        Dim sb As New StringBuilder
        sb.AppendLine(" SELECT ep.containerno,wfs.workflowstepname FROM enginepartplan ep ")
        sb.AppendLine("LEFT JOIN workflowstep wfs ON ep.workflowstepid=wfs.workflowstepid ")
        sb.AppendFormat(" WHERE ep.mfgorderid='{0}' and ep.workflowstepid is not null AND wfs.sequence IN ({1}) AND ep.usecompleted=0 ", mfgid, strSequence)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)
        Return dt
    End Function
#End Region

#Region "装试获取登录人合计工时信息"
    Function GetUserAllWorkTime(ByVal para As Dictionary(Of String, String)) As Double Implements uLMSWorkReportIBusiness.GetUserAllWorkTime
        Dim i_pichan As Double = GetUserWorkTime(para, 0)
        Dim i_shigong As Double = GetUserWorkTime(para, 1)
        Dim i_oil As Double = GetUserWorkTime(para, 2)
        Dim i_shiche As Double = GetUserWorkTime(para, 3)
        Return i_pichan + i_shigong + i_oil + i_shiche
    End Function

    Private Function GetUserWorkTime(ByVal para As Dictionary(Of String, String), ByVal type As Integer) As Double
        GetUserWorkTime = 0
        Dim sb As New StringBuilder
        Dim strslq As String = ""
        If type = 0 Then
            '批产工时
            strslq =
                <string>
                       SELECT NVL(SUM(CASE
                                 WHEN (WT.STEPINFOID IS NOT NULL AND CSW.CHECKTYPE = '1') THEN
                                  COALESCE(WTS.SPLITDURATION,
                                           WT.TOTALDURATION,
                                           ROUND(SI.DURATIONPERUNIT / NVL(WT.SPLITCOUNT, 1), 2),
                                           0)
                                 WHEN (WT.STEPINFOID IS NOT NULL AND CSW.CHECKTYPE = '2') THEN
                                  COALESCE(WTS.SPLITDURATION,
                                           WT.TOTALDURATION,
                                           ROUND(SI.DURATIONPERUNIT2 / NVL(WT.SPLITCOUNT, 1), 2),
                                           0)
                                 ELSE
                                  COALESCE(WT.TOTALDURATION,
                                           WT.DURATIONPERUNIT,
                                           WTS.SPLITDURATION,
                                           ROUND(SD.DURATIONPERUNIT / NVL(WT.SPLITCOUNT, 1), 2),
                                           0)
                               END),
                               0) EMPLOYEEWORKTIME                      
                      FROM CONTAINERSTEPTIMESHEET WT     
                      LEFT JOIN WORKFLOWSTEP WS
                        ON WS.WORKFLOWSTEPID = WT.WORKFLOWSTEPID
                      LEFT JOIN STEPINFO SI
                        ON SI.STEPINFOID = WT.STEPINFOID
                      LEFT JOIN STEPSCHEDULINGDETAIL SD
                        ON SD.STEPID = WT.WORKFLOWSTEPID
                      LEFT JOIN CONTAINERCHECKINFO CKI
                        ON CKI.CONTAINERCHECKINFOID = WT.CONTAINERCHECKINFOID
                      LEFT JOIN CONTAINERSTEPTIMESHEETSPLIT WTS
                        ON WTS.CONTAINERSTEPTIMESHEETID = WT.CONTAINERSTEPTIMESHEETID
                      LEFT JOIN CONTAINERSTEPWORKREPORT CSW
                        ON CKI.CONTAINERSTEPWORKREPORTID = CSW.CONTAINERSTEPWORKREPORTID
                     INNER JOIN ENGINEPARTPLAN EP
                        ON NVL(EP.ENGINECONTAINERID, EP.CONTAINERID) = WT.CONTAINERID
                     WHERE EP.TYPE = 1
                   </string>
            sb.AppendLine(strslq)
            sb.AppendFormat(" AND NVL(wts.workerid,WT.Workerid) ='{0}' ", para("EmployeeId"))
            sb.AppendFormat(" and WT.CREATEDATE >=to_date('{0}','yyyy-mm-dd')  and WT.CREATEDATE <to_date('{1}','yyyy-mm-dd')+1", para("BeginDate"), para("EndDate"))
        ElseIf type = 1 Then
            '施工
            strslq =
                <string>
                    SELECT NVL(SUM(CASE
                                 WHEN (WT.STEPINFOID IS NOT NULL) THEN
                                  COALESCE(WTS.SPLITDURATION,
                                           WT.TOTALDURATION,
                                           ROUND(SI.DURATIONPERUNIT / NVL(WT.SPLITCOUNT, 1), 2),
                                           0)
                                 ELSE
                                  COALESCE(WT.TOTALDURATION,
                                           WT.DURATIONPERUNIT,
                                           WTS.SPLITDURATION,
                                           ROUND(SD.DURATIONPERUNIT / NVL(WT.SPLITCOUNT, 1), 2),
                                           0)
                               END),
                               0) EMPLOYEEWORKTIME                      
                      FROM CONTAINERSTEPTIMESHEET WT         
                      LEFT JOIN STEPSCHEDULINGDETAIL SD
                        ON SD.STEPID = WT.WORKFLOWSTEPID
                      LEFT JOIN STEPINFO SI
                        ON SI.STEPINFOID = WT.STEPINFOID
                      LEFT JOIN CONTAINERCHECKINFO CKI
                        ON CKI.CONTAINERCHECKINFOID = WT.CONTAINERCHECKINFOID
                      LEFT JOIN CONTAINERSTEPTIMESHEETSPLIT WTS
                        ON WTS.CONTAINERSTEPTIMESHEETID = WT.CONTAINERSTEPTIMESHEETID
                      LEFT JOIN ENGINEPARTPLAN EP
                        ON EP.ENGINECONTAINERID = WT.CONTAINERID
                      WHERE EP.TYPE IN (2, 3)
                </string>
            sb.AppendLine(strslq)
            sb.AppendFormat(" AND NVL(wts.workerid,WT.Workerid)  ='{0}' ", para("EmployeeId"))
            sb.AppendFormat(" and WT.CREATEDATE >=to_date('{0}','yyyy-mm-dd')  and WT.CREATEDATE <to_date('{1}','yyyy-mm-dd')+1", para("BeginDate"), para("EndDate"))
        ElseIf type = 2 Then
            '油封
            strslq =
                <string>
                     SELECT NVL(SUM(TRUNC(OS.OILSEALDURATIONPERUNIT / NVL(EC.WORKERCOUNT, 1),
                         2)),
                               0)   EMPLOYEEWORKTIME
                      FROM ENGINEPARTOILSEAL ES                     
                      LEFT JOIN OILSEALCYCLEINFO OS
                        ON ES.OILSEALCYCLEINFOID = OS.OILSEALCYCLEINFOID
                     INNER JOIN ENGINEPARTOILSEALWORKER EW
                        ON ES.ENGINEPARTOILSEALID = EW.ENGINEPARTOILSEALID
                      LEFT JOIN (SELECT EW.ENGINEPARTOILSEALID, COUNT(1) WORKERCOUNT
                                   FROM ENGINEPARTOILSEALWORKER EW
                                  GROUP BY EW.ENGINEPARTOILSEALID) EC
                        ON EC.ENGINEPARTOILSEALID = ES.ENGINEPARTOILSEALID                   
                </string>
            sb.AppendLine(strslq)
            sb.AppendFormat(" WHERE EW.EmployeeId ='{0}' ", para("EmployeeId"))
            sb.AppendFormat(" and ES.CREATEDATE >=to_date('{0}','yyyy-mm-dd')  and ES.CREATEDATE <to_date('{1}','yyyy-mm-dd')+1", para("BeginDate"), para("EndDate"))
        ElseIf type = 3 Then
            '试车
            strslq =
                <string>
                     SELECT NVL(SUM(ST.DURATIONQTY), 0) EMPLOYEEWORKTIME                      
                      FROM TDM_STEPTIMESHEET ST
                      LEFT JOIN EMPLOYEE E
                        ON ST.EMPLOYEENAME = E.EMPLOYEENAME                    
                </string>
            sb.AppendLine(strslq)
            sb.AppendFormat(" WHERE E.EmployeeId ='{0}' ", para("EmployeeId"))
            sb.AppendFormat(" and ST.completedate >=to_date('{0}','yyyy-mm-dd')  and ST.completedate <to_date('{1}','yyyy-mm-dd')+1", para("BeginDate"), para("EndDate"))
        End If
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(sb.ToString)

        Dim i_double As Double = 0
        If Double.TryParse(dt.Rows(0)("EMPLOYEEWORKTIME"), i_double) Then
            GetUserWorkTime = i_double
        Else
            GetUserWorkTime = 0
        End If


    End Function

#End Region


#Region "流水卡工序报工 add by zhangrj 20220629"
    ''' <summary>
    ''' 获取报工数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewStepWorkReportList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSWorkReportIBusiness.GetNewStepWorkReportList
        Dim result As New uMESProcessResult
        Try
            Dim strSql As String
            strSql = <string>
SELECT distinct c.mfgordername, c.containerid,c.containername,c.containertype,c.workflowstepname,c.qty,
c.stepchecknum,c.productname
FROM containercurrentinfo c
LEFT JOIN containerstepdispatch csd ON csd.containerid = c.containerid AND csd.isuse = 0
LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID
LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID 
 WHERE C.DISPATCHNUM = 1 AND C.SPLITNUM = 0 AND NVL(CSD.ISCOMPLETE,0) = 0 AND C.FACTORYID='{0}' 
AND ((CSD.WORKERID IS NOT NULL AND CSD.WORKERID = '{1}') OR (CSD.WORKERID IS NULL AND CSDD.WORKERID='{1}'))  
AND C.CONTAINERSTATUS=1 AND C.ISINSTORE=0 
AND csd.qty>=c.qty --派工数大于等于在制数（工序派工一次派完的）
 AND ws.sequence >= c.workflowstepsequence 
                     </string>.Value
            strSql = String.Format(strSql, para("FactoryID"), para("WorkerID"))

            If para.Keys.Contains("ScanContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ScanContainerName")) Then
                    strSql += " AND ((C.containertype <> '7' and C.containername = '" + para("ScanContainerName") + "')"
                    strSql += " or (C.containertype = '7' and C.togetherflag = '" + para("ScanContainerName") + "'))"
                End If
            End If

            If para.Keys.Contains("ContainerName") Then
                If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
                    strSql += " AND C.containername like '%" + para("ContainerName") + "%'"
                End If
            End If
            strSql += " ORDER BY C.containerid"
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("PageIndex").ToString)
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strSql.ToString
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Catch ex As Exception
            result.Result = False
            result.Message = ex.Message
        End Try
        Return result
    End Function

    ''' <summary>
    ''' 获取批次的派工记录（具体到工厂，工人）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetNewStepWorkReportByContainer(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.GetNewStepWorkReportByContainer
        Dim strSql As String
        strSql = <string>
SELECT WS.WORKFLOWSTEPID,csd.containerstepdispatchid
FROM CONTAINERCURRENTINFO C 
LEFT JOIN CONTAINERSTEPDISPATCH CSD ON CSD.CONTAINERID = C.CONTAINERID AND CSD.ISUSE = 0
LEFT JOIN WORKFLOWSTEP WS ON WS.WORKFLOWSTEPID = CSD.WORKFLOWSTEPID
LEFT JOIN CONTAINERSTEPDISPATCHDETAIL CSDD ON CSDD.CONTAINERSTEPDISPATCHID = CSD.CONTAINERSTEPDISPATCHID 
 WHERE C.DISPATCHNUM = 1 AND C.SPLITNUM = 0 AND NVL(CSD.ISCOMPLETE,0) = 0 AND C.FACTORYID='{0}' 
AND ((CSD.WORKERID IS NOT NULL AND CSD.WORKERID = '{1}') OR (CSD.WORKERID IS NULL AND CSDD.WORKERID='{1}'))  
AND C.CONTAINERSTATUS=1 AND C.ISINSTORE=0 AND CSD.qty>=C.qty
 AND ws.sequence >= c.workflowstepsequence 
                     </string>.Value
        strSql = String.Format(strSql, para("FactoryID"), para("WorkerID"))
        strSql += " AND C.containerid = '" + para("Containerid") + "'"
        Return DatabaseHelper.Query_ReturnDatTable(strSql.ToString)
    End Function

    ''' <summary>
    ''' 验证是否存在报工
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsExistWorkReported(ByVal para As Dictionary(Of String, String)) As Boolean Implements uLMSWorkReportIBusiness.IsExistWorkReported
        Dim strSql As String = "SELECT c.containerstepworkreportid FROM containerstepworkreport c WHERE c.isuse = 0 "
        strSql += String.Format(" AND c.containerid = '{0}'", para("ContainerId").ToString)
        strSql += String.Format(" AND c.workflowstepid = '{0}'", para("WorkflowstepId").ToString)
        Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Rows.Count > 0 Then
            Return True
        End If
        Return False
    End Function
#End Region

#Region "工步报工 add by zhangrj 20220802"
    ''' <summary>
    ''' 获取工步报工信息（未启用，三分厂可用，启用待优化）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoReportList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSWorkReportIBusiness.GetStepInfoReportList
        Dim strSql1 As String = <string>
 SELECT cci.mfgordername,cci.mfgorderid,(cci.productname||'/'||cci.containerno) ProductBatch,cci.workflowid,cci.workflowrevision,
cci.productid,cci.productname,ws.workflowstepname AS nowworkflowstepname,ws.workflowstepid,cci.workflowstepname,cci.qty AS wipqty,csd2.qty,
cci.TOGETHERFLAG,cci.CONTAINERTYPE,
cci.PLANQTY,cci.FACTORYID,s.iskey,cci.CONTAINERID,cci.CONTAINERNAME,csr1.disworkqty,
(CASE WHEN csd2.qty {3} (cci.qty-csr.stepworkqty) OR csd2.qty = cci.qty THEN csd2.qty ELSE cci.qty-csr.stepworkqty END) - csr1.disworkqty AS canreportqty,
cci.ALLLOSSQTY,cci.BOMID,CSD2.CONTAINERSTEPDISPATCHID,CSD2.RESOURCEID,
cci.FIRSTCHECKNUM,cci.STEPCHECKNUM,CSD2.PRIORITYCODE,cci.PRODUCTDESCRIPTION,cci.PRODUCTREVISION,CSD2.PLANNEDSTARTDATE,CSD2.PLANNEDFINISHDATE,
trunc(CSD2.createdate) DispatchDate,s1.description stepinfoname,s1.stepinfoid,s1.stepseq
FROM containerstepdispatch2 csd2
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd2.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd2.workflowstepid
LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID
LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)
LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd2.stepinfoid
LEFT JOIN stepinfobase sb1 ON sb1.stepinfobaseid = s1.stepinfobaseid
LEFT JOIN containerstepdispatchdetail2 csdd2 ON csdd2.containerstepdispatchid = csd2.containerstepdispatchid
LEFT JOIN (--连接查询工步已报工的数据
SELECT csd22.containerstepdispatchid,SUM(nvl(csw.qty,0)) stepworkqty FROM containerstepdispatch2 csd22
LEFT JOIN containerstepworkreport2 csw ON csw.containerid = csd22.containerid AND csw.workflowstepid = csd22.workflowstepid 
AND csw.stepinfoid = csd22.stepinfoid AND csw.isuse = 0
WHERE csd22.isuse = 0
GROUP BY csd22.containerstepdispatchid
) csr ON csr.containerstepdispatchid = csd2.containerstepdispatchid
LEFT JOIN (--连接查询派工已报工的数据
SELECT csd22.containerstepdispatchid,SUM(nvl(csw.qty,0)) disworkqty FROM containerstepdispatch2 csd22
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid = csd22.containerstepdispatchid AND csw.isuse = 0
WHERE csd22.isuse = 0
GROUP BY csd22.containerstepdispatchid
) csr1 ON csr1.containerstepdispatchid = csd2.containerstepdispatchid
WHERE cci.containerstatus = 1 AND cci.isinstore =0 --AND cci.dispatchnum = 1
AND cci.splitnum = 0 AND cci.factoryid = '{0}' 
AND csd2.isuse = 0 AND nvl(csd2.iscomplete,0) = 0
AND ((csd2.WORKERID IS NOT NULL AND csd2.WORKERID = '{1}') OR (csd2.WORKERID IS NULL AND csdd2.WORKERID='{1}'))                       
AND cci.containertype {2} '7'      
                                  </string>.Value

        Dim strSql2 As String = <string>
SELECT cci.mfgordername,cci.mfgorderid,(cci.productname||'/'||cci.containerno) ProductBatch,cci.workflowid,cci.workflowrevision,
cci.productid,cci.productname,ws.workflowstepname AS nowworkflowstepname,ws.workflowstepid,cci.workflowstepname,sum(cci.qty) AS wipqty,sum(nvl(csd2.qty,0)) qty,
cci.TOGETHERFLAG,cci.CONTAINERTYPE,cci.PLANQTY,cci.FACTORYID, NULL iskey,'' CONTAINERID,'' CONTAINERNAME,
SUM(nvl(csr2.qty,0)) AS disworkqty,sum(cci.qty)- SUM(nvl(csr2.qty,0)) AS canreportqty,
NULL ALLLOSSQTY,'' BOMID,'' CONTAINERSTEPDISPATCHID,'' RESOURCEID,
NULL FIRSTCHECKNUM,NULL STEPCHECKNUM,NULL PRIORITYCODE,cci.PRODUCTDESCRIPTION,cci.PRODUCTREVISION,NULL PLANNEDSTARTDATE,NULL PLANNEDFINISHDATE,
NULL DispatchDate,s1.description stepinfoname,s1.stepinfoid,s1.stepseq
FROM containerstepdispatch2 csd2
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd2.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd2.workflowstepid
LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID
LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)
LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd2.stepinfoid
LEFT JOIN stepinfobase sb1 ON sb1.stepinfobaseid = s1.stepinfobaseid
LEFT JOIN containerstepdispatchdetail2 csdd2 ON csdd2.containerstepdispatchid = csd2.containerstepdispatchid
LEFT JOIN containerstepworkreport2 csr2 ON csr2.containerid = csd2.containerid AND csr2.workflowstepid = csd2.workflowstepid 
AND csr2.stepinfoid = csd2.stepinfoid AND csr2.isuse = 0
WHERE cci.containerstatus = 1 AND cci.isinstore =0 --AND cci.dispatchnum = 1
AND cci.splitnum = 0 AND cci.factoryid = '{0}' 
AND csd2.isuse = 0 AND nvl(csd2.iscomplete,0) = 0
AND ((csd2.WORKERID IS NOT NULL AND csd2.WORKERID = '{1}') OR (csd2.WORKERID IS NULL AND csdd2.WORKERID='{1}'))  
AND cci.containertype = '7' 
                                </string>.Value

        strSql1 = String.Format(strSql1, para("FactoryID").ToString, para("WorkerID").ToString, "<>", "<=")
        strSql2 = String.Format(strSql2, para("FactoryID").ToString, para("WorkerID").ToString)

        If Not String.IsNullOrWhiteSpace(para("ScanContainerName").ToString) Then
            strSql1 += " and cci.containername = '" + para("ScanContainerName") + "'"
            strSql2 += " and cci.togetherflag = '" + para("ScanContainerName") + "'"
        End If

        If para("CurrentWorkflowStep") Then '如果是待报工，只显示当前工序
            strSql1 += " and ws.sequence = cci.workflowstepsequence "
            strSql2 += " and ws.sequence = cci.workflowstepsequence "
        Else
            strSql1 += " and ws.sequence >= cci.workflowstepsequence "
            strSql2 += " and ws.sequence >= cci.workflowstepsequence "
        End If

        strSql2 += <string>
GROUP BY cci.mfgordername,cci.mfgorderid,cci.productname,cci.containerno,cci.productid,cci.productname,cci.workflowid,cci.workflowrevision,
ws.workflowstepname,ws.workflowstepid,cci.workflowstepname,cci.qty,cci.TOGETHERFLAG,cci.CONTAINERTYPE,cci.PLANQTY,cci.FACTORYID,
s1.description,s1.stepinfoid,cci.PRODUCTDESCRIPTION,cci.PRODUCTREVISION,s1.stepseq
                   </string>.Value

        Dim strSql As String = "SELECT ck.*,DECODE(ck.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME FROM (" + strSql1.ToString + " UNION " + strSql2.ToString + ") ck WHERE ck.canreportqty > 0 order by ck.stepseq"
        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取工步报工信息（热表）
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetRBStepInfoReportList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements IBusiness.uLMSWorkReportIBusiness.GetRBStepInfoReportList
        Dim strSql As String = <string>SELECT * FROM (
 SELECT cci.mfgordername,cci.mfgorderid,(cci.productname||'/'||cci.containerno) ProductBatch,cci.workflowid,cci.workflowrevision,
cci.productid,cci.productname,ws.workflowstepname AS nowworkflowstepname,ws.workflowstepid,cci.workflowstepname,cci.qty AS wipqty,csd2.qty,
cci.TOGETHERFLAG,cci.CONTAINERTYPE,cci.PLANQTY,cci.FACTORYID,cci.CONTAINERID,cci.CONTAINERNAME,csr1.disworkqty,
CASE WHEN cci.qty >= csd2.qty THEN csd2.qty - csr1.disworkqty ELSE cci.qty - csr1.disworkqty END AS canreportqty,
cci.ALLLOSSQTY,cci.BOMID,CSD2.CONTAINERSTEPDISPATCHID,CSD2.RESOURCEID,
cci.FIRSTCHECKNUM,cci.STEPCHECKNUM,CSD2.PRIORITYCODE,cci.PRODUCTDESCRIPTION,cci.PRODUCTREVISION,CSD2.PLANNEDSTARTDATE,CSD2.PLANNEDFINISHDATE,
trunc(CSD2.createdate) DispatchDate,s1.description stepinfoname,s1.stepinfoid,s1.stepseq,s1.revision stepinforev,
DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
FROM containerstepdispatch2 csd2
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd2.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd2.workflowstepid
LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd2.stepinfoid
LEFT JOIN stepinfobase sb1 ON sb1.stepinfobaseid = s1.stepinfobaseid
LEFT JOIN containerstepdispatchdetail2 csdd2 ON csdd2.containerstepdispatchid = csd2.containerstepdispatchid
LEFT JOIN (--连接查询派工已报工的数据
SELECT csd22.containerstepdispatchid,SUM(nvl(csw.qty,0)) disworkqty FROM containerstepdispatch2 csd22
LEFT JOIN containerstepworkreport2 csw ON csw.containerstepdispatchid = csd22.containerstepdispatchid AND csw.isuse = 0
WHERE csd22.isuse = 0
GROUP BY csd22.containerstepdispatchid
) csr1 ON csr1.containerstepdispatchid = csd2.containerstepdispatchid
WHERE cci.containerstatus = 1 AND cci.isinstore = 0 AND cci.factoryid = '{0}' AND csd2.isuse = 0
AND ((csd2.WORKERID IS NOT NULL AND csd2.WORKERID = '{1}') OR (csd2.WORKERID IS NULL AND csdd2.WORKERID='{1}'))                       
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = csd2.containerid)
{2}
)k WHERE k.canreportqty > 0
                                  </string>.Value

        Dim strWhere = String.Empty
        If Not String.IsNullOrWhiteSpace(para("ScanContainerName").ToString) Then
            strWhere += " and cci.containername = '" + para("ScanContainerName") + "'"
        End If

        If para("CurrentWorkflowStep") Then '如果是待报工，只显示当前工序
            strWhere += " and ws.sequence = cci.workflowstepsequence "
        Else
            strWhere += " and ws.sequence >= cci.workflowstepsequence "
        End If

        strSql = String.Format(strSql, para("FactoryID").ToString, para("WorkerID").ToString, strWhere)

        strSql += " order by k.stepseq"

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSql
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 获取单件单卡批次的工步数据
    ''' </summary>
    ''' <param name="para"></param>mv
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetSingleContainerStepInfoReportList(ByVal para As DataRow) As DataTable Implements uLMSWorkReportIBusiness.GetSingleContainerStepInfoReportList
        Dim strSql As String = <string>
SELECT cci.mfgordername,cci.mfgorderid,(cci.productname||'/'||cci.containerno) ProductBatch,cci.workflowid,cci.workflowrevision,
cci.productid,cci.productname,ws.workflowstepname AS nowworkflowstepname,ws.workflowstepid,cci.workflowstepname,cci.qty AS wipqty,csd2.qty,
cci.TOGETHERFLAG,cci.CONTAINERTYPE,
cci.PLANQTY,cci.FACTORYID,s.iskey,cci.CONTAINERID,cci.CONTAINERNAME,0 disworkqty,csd2.qty canreportqty,
cci.ALLLOSSQTY,cci.BOMID,CSD2.CONTAINERSTEPDISPATCHID,CSD2.RESOURCEID,
cci.FIRSTCHECKNUM,cci.STEPCHECKNUM,CSD2.PRIORITYCODE,cci.PRODUCTDESCRIPTION,cci.PRODUCTREVISION,CSD2.PLANNEDSTARTDATE,CSD2.PLANNEDFINISHDATE,
trunc(CSD2.createdate) DispatchDate,s1.description stepinfoname,s1.stepinfoid,s1.revision stepinforev
FROM containerstepdispatch2 csd2
LEFT JOIN containercurrentinfo cci ON cci.containerid = csd2.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd2.workflowstepid
LEFT JOIN SPECBASE SB ON SB.SPECBASEID = WS.SPECBASEID
LEFT JOIN SPEC S ON S.SPECID = NVL(SB.REVOFRCDID, WS.SPECID)
LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd2.stepinfoid
LEFT JOIN stepinfobase sb1 ON sb1.stepinfobaseid = s1.stepinfobaseid
LEFT JOIN containerstepdispatchdetail2 csdd2 ON csdd2.containerstepdispatchid = csd2.containerstepdispatchid
LEFT JOIN containerstepworkreport2 csr2 ON csr2.containerstepdispatchid = csd2.containerstepdispatchid AND csr2.isuse = 0
WHERE cci.containerstatus = 1 AND cci.isinstore =0 --AND cci.dispatchnum = 1 
and csr2.containerstepworkreportid IS NULL
AND cci.splitnum = 0 AND cci.factoryid = '{0}' 
AND csd2.isuse = 0 AND nvl(csd2.iscomplete,0) = 0
AND ((csd2.WORKERID IS NOT NULL AND csd2.WORKERID = '{1}') OR (csd2.WORKERID IS NULL AND csdd2.WORKERID='{1}'))  
AND cci.containertype = '7' 
                                </string>.Value
        strSql = String.Format(strSql, para("FactoryID").ToString, para("WorkerID").ToString)
        strSql += " and cci.productid = '" + para("ProductId").ToString + "'"
        strSql += " and cci.PRODUCTREVISION = '" + para("PRODUCTREVISION").ToString + "'"
        strSql += " and cci.workflowid = '" + para("WorkflowId").ToString + "'"
        strSql += " and cci.workflowrevision = '" + para("Workflowrevision").ToString + "'"
        strSql += " and cci.workflowstepid = '" + para("WorkflowstepId").ToString + "'"
        strSql += " and s1.stepinfoid = '" + para("StepinfoId").ToString + "'"
        strSql += " and cci.togetherflag = '" + para("TogetherFlag").ToString + "'"
        strSql += " order by cci.containerid"
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 填充工步报工数据
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <param name="value"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillContainerStepInfoWorkReportDataTrans(ByVal entity As ContainerStepWorkReportDTO, ByVal value As String) As Object() Implements IBusiness.uLMSWorkReportIBusiness.FillContainerStepInfoWorkReportDataTrans
        Dim strsql As String =
        <string>
                INSERT INTO CONTAINERSTEPWORKREPORT2(CONTAINERID,FACTORYID,WORKCENTERID,TEAMID,WORKFLOWSTEPID,RESOURCEID,WORKERID,CHECKERID,QTY,CHECKTYPE,CONTAINERSTEPDISPATCHID,
                CONTAINERSTEPWORKREPORTID,CREATEDATE,ISUSE,SELFCHECKOUTOFTOLERANCEQTY,SELFCHECKLOSSQTY,SELFCHECKQUALIFIEDQTY,ISTRANSFER,Qty2,UOM2ID,SurplusEmployeeID,STEPINFOID,Specimenqty,Specimenlossqty,
                reasontype,reasondesc,firstcheckcontainerno,keyprocessbatchno,notes)
                VALUES(:Containerid,:Factoryid,:Workcenterid,:Teamid,:Workflowstepid,:Resourceid,:Workerid,:Checkerid,:Qty,:Checktype,:Containerstepdispatchid,
                :Containerstepworkreportid,:Createdate,:Isuse,:SELFCHECKOUTOFTOLERANCEQTY,:SELFCHECKLOSSQTY,:SELFCHECKQUALIFIEDQTY,:ISTRANSFER,:Qty2,:UOM2ID,
                :SurplusEmployeeID,:STEPINFOID,:Specimenqty,:Specimenlossqty,:reasontype,:reasondesc,:firstcheckcontainerno,:keyprocessbatchno,:notes)
        </string>
        Dim insertpar(28) As OracleParameter
        insertpar(0) = DatabaseHelper.NewParameter(":Containerid", entity.ContainerID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":Factoryid", entity.FactoryID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":Workcenterid", entity.WorkCenterID, OracleType.VarChar)
        insertpar(3) = DatabaseHelper.NewParameter(":Teamid", entity.TeamID, OracleType.VarChar)
        insertpar(4) = DatabaseHelper.NewParameter(":Workflowstepid", entity.WorkflowstepID, OracleType.VarChar)
        insertpar(5) = DatabaseHelper.NewParameter(":Resourceid", entity.ResourceID, OracleType.VarChar)
        insertpar(6) = DatabaseHelper.NewParameter(":Workerid", entity.WorkerID, OracleType.VarChar)
        insertpar(7) = DatabaseHelper.NewParameter(":Checkerid", entity.CheckerID, OracleType.VarChar)
        insertpar(8) = DatabaseHelper.NewParameter(":Qty", entity.Qty, OracleType.Number)
        insertpar(9) = DatabaseHelper.NewParameter(":Checktype", entity.CheckType, OracleType.Number)
        insertpar(10) = DatabaseHelper.NewParameter(":Containerstepdispatchid", entity.ContainerStepDispatchID, OracleType.VarChar)
        insertpar(11) = DatabaseHelper.NewParameter(":Containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        If entity.CreateDate = DateTime.Parse("0001/1/1") Then
            insertpar(12) = DatabaseHelper.NewParameter(":Createdate", System.DateTime.Now, OracleType.DateTime)
        Else
            insertpar(12) = DatabaseHelper.NewParameter(":Createdate", entity.CreateDate, OracleType.DateTime)
        End If
        insertpar(13) = DatabaseHelper.NewParameter(":Isuse", 0, OracleType.Number)
        '
        insertpar(14) = DatabaseHelper.NewParameter(":SELFCHECKOUTOFTOLERANCEQTY", entity.SelfCheckOutofToleranceQty, OracleType.Number)
        insertpar(15) = DatabaseHelper.NewParameter(":SELFCHECKLOSSQTY", entity.SelfCheckLossQty, OracleType.Number)
        insertpar(16) = DatabaseHelper.NewParameter(":SELFCHECKQUALIFIEDQTY", entity.SeleCheckQualifiedQty, OracleType.Number)
        If value = "split" Then
            insertpar(17) = DatabaseHelper.NewParameter(":ISTRANSFER", 1, OracleType.Number)
        Else
            insertpar(17) = DatabaseHelper.NewParameter(":ISTRANSFER", 0, OracleType.Number)
        End If
        If Not entity.UOM2ID Is Nothing And entity.Qty2 <> 0 Then
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", entity.Qty2, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", entity.UOM2ID, OracleType.VarChar)
        Else
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", DBNull.Value, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.SurplusEmployeeID Is Nothing Then
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", entity.SurplusEmployeeID, OracleType.VarChar)
        Else
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.UOM2ID Is Nothing And entity.Qty2 <> 0 Then
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", entity.Qty2, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", entity.UOM2ID, OracleType.VarChar)
        Else
            insertpar(18) = DatabaseHelper.NewParameter(":Qty2", DBNull.Value, OracleType.Number)
            insertpar(19) = DatabaseHelper.NewParameter(":UOM2ID", DBNull.Value, OracleType.VarChar)
        End If
        If Not entity.SurplusEmployeeID Is Nothing Then
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", entity.SurplusEmployeeID, OracleType.VarChar)
        Else
            insertpar(20) = DatabaseHelper.NewParameter(":SurplusEmployeeID", DBNull.Value, OracleType.VarChar)
        End If
        If entity.StepInfoID IsNot Nothing Then
            insertpar(21) = DatabaseHelper.NewParameter(":STEPINFOID", entity.StepInfoID, OracleType.VarChar)
        Else
            insertpar(21) = DatabaseHelper.NewParameter(":STEPINFOID", DBNull.Value, OracleType.VarChar)
        End If
        If entity.Specimenqty <> 0 Then
            insertpar(22) = DatabaseHelper.NewParameter(":Specimenqty", entity.Specimenqty, OracleType.Number)
        Else
            insertpar(22) = DatabaseHelper.NewParameter(":Specimenqty", DBNull.Value, OracleType.Number)
        End If
        If entity.Specimenlossqty <> 0 Then
            insertpar(23) = DatabaseHelper.NewParameter(":Specimenlossqty", entity.Specimenlossqty, OracleType.Number)
        Else
            insertpar(23) = DatabaseHelper.NewParameter(":Specimenlossqty", DBNull.Value, OracleType.Number)
        End If

        insertpar(24) = DatabaseHelper.NewParameter("reasontype", IIf(IsNothing(entity.ReasonType), String.Empty, entity.ReasonType), OracleType.VarChar)
        insertpar(25) = DatabaseHelper.NewParameter("reasondesc", IIf(IsNothing(entity.ReasonDesc), String.Empty, entity.ReasonDesc), OracleType.VarChar)
        If entity.FirstContainerNo IsNot Nothing Then
            insertpar(26) = DatabaseHelper.NewParameter(":firstcheckcontainerno", entity.FirstContainerNo, OracleType.VarChar)
        Else
            insertpar(26) = DatabaseHelper.NewParameter(":firstcheckcontainerno", DBNull.Value, OracleType.VarChar)
        End If
        If entity.KeyProcessBatchNo IsNot Nothing Then
            insertpar(27) = DatabaseHelper.NewParameter(":keyprocessbatchno", entity.KeyProcessBatchNo, OracleType.VarChar)
        Else
            insertpar(27) = DatabaseHelper.NewParameter(":keyprocessbatchno", DBNull.Value, OracleType.VarChar)
        End If
        If entity.Note IsNot Nothing Then
            insertpar(28) = DatabaseHelper.NewParameter(":notes", entity.Note, OracleType.VarChar)
        Else
            insertpar(28) = DatabaseHelper.NewParameter(":notes", DBNull.Value, OracleType.VarChar)
        End If
        Return New Object() {strsql, insertpar}
    End Function

    ''' <summary>
    ''' 填充工步派工表接收人
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillReceviceDispatch2(ByVal para As Dictionary(Of String, String)) As Object() Implements IBusiness.uLMSWorkReportIBusiness.FillReceviceDispatch2
        Dim strsql As New StringBuilder
        If para("type") = "1" Then
            strsql.AppendLine("UPDATE CONTAINERSTEPDISPATCH2 C SET C.RECVDATE = SYSDATE,")
            strsql.AppendFormat(" C.RECVEMPLOYEEID ='{0}' WHERE ", para("WorkerID"))
            strsql.AppendFormat(" C.CONTAINERSTEPDISPATCHID IN ({0}) ", para("ContainerStepDispatchIDs"))
            strsql.AppendLine(" AND C.RECVEMPLOYEEID IS NULL")
        ElseIf para("type") = "2" Then
            strsql.AppendFormat(" UPDATE CONTAINERSTEPDISPATCH2 C SET C.RECVEMPLOYEEID ='{0}'", para("WorkerID"))
            strsql.AppendFormat(" WHERE C.CONTAINERSTEPDISPATCHID ='{0}'", para("ContainerStepDispatchID"))
        End If
        Return New Object() {strsql.ToString, Nothing}
    End Function

    ''' <summary>
    ''' 更新工人可能录入的检测项(工步)
    ''' </summary>
    ''' <param name="dto"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateStepInfoCheckPointReport(ByVal dto As ContainerStepWorkReportDTO) As Object() Implements uLMSWorkReportIBusiness.UpdateStepInfoCheckPointReport
        Dim strSql = " update stepcheckpointresult sc set sc.containerstepworkreportid='{0}' where sc.containerid='{1}' and sc.workerid='{2}' and sc.containerstepworkreportid is null and sc.stepcheckpointid in "
        strSql += " (select st.stepcheckpointid from stepcheckpoint st where st.workflowstepid='{3}' "
        strSql = String.Format(strSql, dto.ContainerStepWorkReportID, dto.ContainerID, dto.WorkerID, dto.WorkflowstepID)
        strSql += " and st.stepinfoid='" + dto.StepInfoID + "')"
        Return New Object() {strSql, Nothing}
    End Function

    ''' <summary>
    ''' 插入明细表
    ''' </summary>
    ''' <param name="entity"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FillContainerStepInfoWorkReportChild2(ByVal entity As ContainerStepWorkReportChildDTO) As Object() Implements IBusiness.uLMSWorkReportIBusiness.FillContainerStepInfoWorkReportChild2
        Dim strsql As String =
            <string>
                INSERT INTO CONTAINERSTEPWORKREPORTCHILD2(CONTAINERSTEPWORKREPORTID,CHILDCONTAINERID,SELFCHECKLOSSQTY) VALUES(:Containerstepworkreportid,:Childcontainerid,:Selfchecklossqty)
            </string>
        Dim insertpar(2) As OracleParameter
        'modify 2017/10/10 
        insertpar(0) = DatabaseHelper.NewParameter(":Containerstepworkreportid", entity.ContainerStepWorkReportID, OracleType.VarChar)
        insertpar(1) = DatabaseHelper.NewParameter(":Childcontainerid", entity.ChildContainerID, OracleType.VarChar)
        insertpar(2) = DatabaseHelper.NewParameter(":Selfchecklossqty", entity.SelfCheckLossQty, OracleType.VarChar)
        '
        Return New Object() {strsql, insertpar}

    End Function

    ''' <summary>
    ''' 获取工步未报工子序号
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetContainerStepInfoNoReportChildList(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSWorkReportIBusiness.GetContainerStepInfoNoReportChildList
        Dim strSql = <string>
SELECT distinct c.containerid,c.containername  FROM container c
WHERE c.parentcontainerid = '{0}' and c.containerid not in (
select cswd.childcontainerid from containerstepworkreport2 csw 
LEFT JOIN containerstepworkreportchild2 cswd ON cswd.containerstepworkreportid = csw.containerstepworkreportid
where csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.stepinfoid = '{2}' and csw.isuse = 0
) and c.status = 1
                     </string>.Value
        strSql = String.Format(strSql, para("ContainerId").ToString, para("WorkflowStepId").ToString, para("StepInfoId").ToString)
        Return DatabaseHelper.Query_ReturnDatTable(strSql)
    End Function

    ''' <summary>
    ''' 获取可撤销的工步报工 add by zhangrj 2022-09-21
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoDeletableReportInfo(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSWorkReportIBusiness.GetStepInfoDeletableReportInfo
        Dim strSql = <string>
                         SELECT csw.containerstepworkreportid,cci.containername,cci.containerid,cci.qty wipqty, s.description StepInfoDesc, csw.qty ReportQty,
e.fullname WorkerName,ws.workflowstepname,ws.workflowstepid,to_char(csw.createdate,'yyyy-MM-dd') ReportDate
,s.stepinfoid,decode(csw.checktype,'1','首检','2','序检','3','试件报工','4','返修','') checktype
FROM containerstepworkreport2 csw 
LEFT JOIN employee e ON e.employeeid = csw.workerid
LEFT JOIN containercurrentinfo cci ON cci.containerid = csw.containerid
LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csw.containerstepworkreportid AND cc.isuse = 0
LEFT JOIN stepinfo s ON s.stepinfoid = csw.stepinfoid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = s.workflowstepid
WHERE csw.isuse = 0 AND csw.workerid = '{0}' AND csw.factoryid = '{1}'
AND cc.containercheckinfoid IS NULL and cci.containerstatus = 1
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = csw.containerid)
                        </string>.Value
        strSql = String.Format(strSql, para("WorkerID").ToString, para("FactoryID").ToString)

        If Not String.IsNullOrWhiteSpace(para("ScanContainerName").ToString) Then
            strSql += " AND ((cci.containertype <> '7' and cci.containername = '" + para("ScanContainerName") + "')"
            strSql += " or (cci.containertype = '7' and cci.togetherflag = '" + para("ScanContainerName") + "'))"
        End If

        Dim result As New uMESProcessResult
        result.Result = True
        result.DBTable = DatabaseHelper.Query_ReturnDatTable(strSql)
        Return result
    End Function

    ''' <summary>
    ''' 撤销工步报工（工步撤销） add by zhangrj 2022-09-21
    ''' </summary>
    ''' <param name="containerStepWorkReportIDs"></param>
    ''' <param name="list"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepInfoReport2ByID(ByVal containerStepWorkReportIDs As String, ByVal list As List(Of Dictionary(Of String, String)), ByRef msg As String) As Boolean Implements uLMSWorkReportIBusiness.DelContainerStepInfoReport2ByID
        Dim strsql As String = <string>
SELECT csw.containerstepworkreportid FROM containerstepworkreport2 csw
LEFT JOIN containercheckinfo2 cc ON cc.containerstepworkreportid = csw.containerstepworkreportid AND cc.isuse = 0
WHERE csw.isuse = 0 AND cc.containercheckinfoid IS NOT NULL AND csw.containerstepworkreportid IN ({0})
                         </string>.Value
        strsql = String.Format(strsql, containerStepWorkReportIDs)
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strsql)
        If dt.Rows.Count > 0 Then '如果有报工已经检验，则不更新
            msg = "存在已检验报工，请撤销检验后刷新页面"
            Return False
            Exit Function
        End If
        Dim sqlList As New ArrayList
        For Each dic As Dictionary(Of String, String) In list
            Dim strsql1 As New StringBuilder
            '删除工步报工
            strsql1.Clear()
            strsql1.AppendFormat(" UPDATE containerstepworkreport2 csr SET csr.ISUSE=1 WHERE csr.containerstepworkreportid = '{0}'", dic("ContainerStepWorkReportID"))
            sqlList.Add(strsql1.ToString)
            '删除实测值
            strsql1.Clear()
            strsql1.AppendFormat(" delete stepcheckpointresult sc WHERE sc.containerstepworkreportid='{0}' AND sc.containerid='{1}'", dic("ContainerStepWorkReportID"), dic("CONTAINERID"))
            strsql1.AppendFormat("  and sc.stepcheckpointid IN (select st.stepcheckpointid from stepcheckpoint st where st.workflowstepid='{0}' and st.stepinfoid='{1}') ", dic("WORKFLOWSTEPID"), dic("StepInfoID"))
            sqlList.Add(strsql1.ToString)
            '删除工序报工
            strsql1.Clear()
            strsql1.AppendLine("UPDATE containerstepworkreport C SET C.ISUSE=1 ")
            strsql1.AppendFormat(" WHERE C.CONTAINERID='{0}' and C.Workflowstepid = '{1}' and C.ISUSE=0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            '没有包含该工序删除，则添加
            If Not sqlList.Contains(strsql1.ToString) Then
                sqlList.Add(strsql1.ToString)
            End If
        Next
        If sqlList.Count > 0 Then
            DatabaseHelper.ExecuteSqlTran(sqlList)
        End If
        msg = "删除成功"
        Return True
    End Function

    ''' <summary>
    ''' 撤销工步报工（通过工序）add by zhangrj 2023-3-7
    ''' </summary>
    ''' <param name="list"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function DelContainerStepInfoReport2BySpec(ByVal list As List(Of Dictionary(Of String, String))) As Boolean Implements uLMSWorkReportIBusiness.DelContainerStepInfoReport2BySpec
        Dim sqlList As New ArrayList
        For Each dic As Dictionary(Of String, String) In list
            Dim strsql1 As New StringBuilder
            Dim querySql = String.Format("select csw.stepinfoid,csw.containerstepworkreportid from containerstepworkreport2 csw where csw.containerid = '{0}' and csw.workflowstepid='{1}' and csw.isuse = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(querySql)
            '如果不存在工步级的报工数据，则跳过（不跳过会删除工序所有报工记录）
            If dt.Rows.Count = 0 Then
                Continue For
            End If

            For Each dr As DataRow In dt.Rows
                '删除实测值
                strsql1.Clear()
                strsql1.AppendFormat("delete stepcheckpointresult sc WHERE sc.containerstepworkreportid='{0}' AND sc.containerid='{1}'", dr("ContainerStepWorkReportID"), dic("CONTAINERID"))
                strsql1.AppendFormat("  and sc.stepcheckpointid IN (select st.stepcheckpointid from stepcheckpoint st where st.workflowstepid='{0}' and st.stepinfoid='{1}') ", dic("WORKFLOWSTEPID"), dr("StepInfoID"))
                If Not sqlList.Contains(strsql1) Then
                    sqlList.Add(strsql1.ToString)
                End If
            Next

            '删除工步报工（工序下所有）
            strsql1.Clear()
            strsql1.AppendFormat("UPDATE containerstepworkreport2 csr SET csr.ISUSE=1 WHERE csr.containerid = '{0}' and csr.workflowstepid='{1}' and csr.isuse = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            If Not sqlList.Contains(strsql1) Then
                sqlList.Add(strsql1.ToString)
            End If

            '删除工步检验（工序下所有）
            strsql1.Clear()
            strsql1.AppendFormat("UPDATE containercheckinfo2 cci SET cci.isuse =1 WHERE cci.containerid = '{0}' AND cci.workflowstepid ='{1}' AND cci.isuse = 0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            If Not sqlList.Contains(strsql1) Then
                sqlList.Add(strsql1.ToString)
            End If

            '删除工序报工
            strsql1.Clear()
            strsql1.AppendLine("UPDATE containerstepworkreport C SET C.ISUSE=1 ")
            strsql1.AppendFormat(" WHERE C.CONTAINERID='{0}' and C.Workflowstepid = '{1}' and C.ISUSE=0", dic("CONTAINERID"), dic("WORKFLOWSTEPID"))
            If Not sqlList.Contains(strsql1) Then
                sqlList.Add(strsql1.ToString)
            End If
        Next
        If sqlList.Count > 0 Then
            DatabaseHelper.ExecuteSqlTran(sqlList)
        End If
        Return True
    End Function

    ''' <summary>
    ''' 获取批量报工工步列表（热表） add by zhangrj 2022-10-12
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepInfoBatchReportList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSWorkReportIBusiness.GetStepInfoBatchReportList
        '转工批次
        Dim strSelect1 = <string>
SELECT distinct csd.containerstepdispatchid,c.containername,c.containerid,ws.workflowstepname,csd.workflowstepid,c.workflowid,c.productid,
s1.stepinfoid,s1.stepno,s1.stepseq,s1.description stepinfoname,csd.createdate,c.qty wipqty,csd.qty disqty,c.containertype,
DECODE(c.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,s1.revision stepinforev
FROM containerstepdispatch2 csd 
LEFT JOIN containercurrentinfo c ON c.containerid = csd.containerid
LEFT JOIN Userattribute u ON u.parentid = c.containerid AND u.userattributename = '热表时效性'
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd.stepinfoid
LEFT JOIN containerstepdispatchdetail2 csdd ON csdd.containerstepdispatchid = csd.containerstepdispatchid
LEFT JOIN containersubcontainer csc ON csc.subcontainerid = c.containerid
LEFT JOIN containercurrentinfo c1 ON c1.containerid = csc.containerid 
LEFT JOIN containershiprecord csr ON csr.containerid = c1.containerid AND csr.isuse = 0 AND csr.toacceptdate IS NOT NULL
LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid = csr.containershiprecordid 
LEFT JOIN workflowstep ws1 ON ws1.workflowstepid = csrs.workflowstepid 
WHERE csd.isuse = 0 AND c.containerstatus = 1 AND c.isinstore =0 AND c.factoryid = '{1}'
AND ((csd.WORKERID IS NOT NULL AND csd.WORKERID = '{0}') OR (csd.WORKERID IS NULL AND csdd.WORKERID='{0}'))   
AND NOT EXISTS(SELECT 1 FROM containerstepworkreport2 csw WHERE csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0)--批量报工只能报没报工的
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = csd.containerid)
AND (CASE WHEN c.containertype = '3' THEN csd.qty ELSE c.qty END) >= c.qty --如果是批量带序号批次，必须派工数大于等于在制数（大于是考虑报废情况）
AND c.qty > 0 AND (ws1.workflowstepid = csc.workflowstepid OR ws1.workflowstepname = csc.workflowstepname) --转工已接收
AND (c1.workflowstepname = csc.workflowstepname OR nvl(u.attributevalue,0) = 1) -- 主卡应该在当前转工序上，或者具有时效性
{2}
                     </string>.Value
        '无订单批次
        Dim strSelect2 = <string>
SELECT csd.containerstepdispatchid,c.containername,c.containerid,ws.workflowstepname,csd.workflowstepid,c.workflowid,c.productid,
s1.stepinfoid,s1.stepno,s1.stepseq,s1.description stepinfoname,csd.createdate,c.qty wipqty,csd.qty disqty,c.containertype,
DECODE(c.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,s1.revision stepinforev
FROM containerstepdispatch2 csd 
LEFT JOIN containercurrentinfo c ON c.containerid = csd.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd.stepinfoid
LEFT JOIN containerstepdispatchdetail2 csdd ON csdd.containerstepdispatchid = csd.containerstepdispatchid
WHERE csd.isuse = 0 AND c.containerstatus = 1 AND c.isinstore =0 AND c.factoryid = '{1}'
AND ((csd.WORKERID IS NOT NULL AND csd.WORKERID = '{0}') OR (csd.WORKERID IS NULL AND csdd.WORKERID='{0}'))   
AND NOT EXISTS(SELECT 1 FROM containerstepworkreport2 csw WHERE csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0)--批量报工只能报没报工的
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = csd.containerid)
AND (CASE WHEN c.containertype = '3' THEN csd.qty ELSE c.qty END) >= c.qty --如果是批量带序号批次，必须派工数大于等于在制数（大于是考虑报废情况）
AND c.qty > 0
AND EXISTS (SELECT 1 FROM Userattribute u WHERE u.parentid = c.containerid AND u.userattributename = '委托单位') --查询无订单批次
{2}
                     </string>.Value
        '合卡批次
        Dim strSelect3 = <string>
SELECT '' containerstepdispatchid, cb.containercombinatename containername,'' containerid,ws.workflowstepname,csd.workflowstepid,c.workflowid,c.productid,
s1.stepinfoid,s1.stepno,s1.stepseq,s1.description stepinfoname,max(csd.createdate) createdate,sum(c.qty) wipqty,sum(csd.qty) disqty,c.containertype,
DECODE(c.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME,s1.revision stepinforev
FROM containercombinate cb 
LEFT JOIN containercurrentinfo c ON c.containerid = cb.containerid
LEFT JOIN containerstepdispatch2 csd ON csd.containerid = cb.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN stepinfo s1 ON s1.stepinfoid = csd.stepinfoid
LEFT JOIN containerstepdispatchdetail2 csdd ON csdd.containerstepdispatchid = csd.containerstepdispatchid
WHERE csd.isuse = 0 AND c.containerstatus = 1 AND c.isinstore = 0 AND c.factoryid = '{1}'
AND ((csd.WORKERID IS NOT NULL AND csd.WORKERID = '{0}') OR (csd.WORKERID IS NULL AND csdd.WORKERID='{0}'))   
AND NOT EXISTS(SELECT 1 FROM containerstepworkreport2 csw WHERE csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0)
AND c.qty > 0
{2}
GROUP BY ws.workflowstepname,csd.workflowstepid,c.workflowid,c.productid,cb.containercombinatename,
s1.stepinfoid,s1.stepno,s1.stepseq,s1.description,c.containertype,s1.revision
                     </string>.Value

        Dim strWhere = String.Empty '查询条件
        Dim strSelect = String.Format(<string>
                                          {0}
                                          UNION ALL
                                          {1}
                                          UNION ALL
                                          {2}
                                      </string>.Value, strSelect1, strSelect2, strSelect3)
        If para.Keys.Contains("CombinateName") Then
            If Not String.IsNullOrWhiteSpace(para("CombinateName")) Then
                strSelect = strSelect3 '如果存在合卡名查询，只查询合卡批次
                strWhere += " AND cb.containercombinatename like '%" + para("CombinateName") + "%'"
            End If
        End If
        '通用查询
        If Not String.IsNullOrWhiteSpace(para("OrderName")) Then
            strWhere += " AND c.mfgordername like '%" + para("OrderName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
            strWhere += " AND c.productname like '%" + para("ProductName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
            strWhere += " AND c.containername LIKE '%" + para("ContainerName") + "%'"
        End If

        strSelect = String.Format(strSelect, para("WorkerID"), para("FactoryID"), strWhere)

        strSelect = String.Format("select * from ({0})k order by k.containername,k.workflowstepid,k.stepseq", strSelect)

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 基于工步排产
    ''' 获取批量报工工步列表（热表） add by zhangrj 2024-9-10
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetApsStepInfoBatchReportList(ByVal para As Dictionary(Of String, String)) As uMESProcessResult Implements uLMSWorkReportIBusiness.GetApsStepInfoBatchReportList
        '转工批次
        Dim strSelect1 = <string>
SELECT DISTINCT csd.containerstepdispatchid,cci.containername,cci.containerid,ws.workflowstepname,csd.workflowstepid,cci.workflowid,cci.productid,
s.stepinfoid,s.stepno,s.stepseq,s.description stepinfoname,s.revision stepinforev,csd.createdate,cci.qty wipqty,csd.qty disqty,cci.containertype,
DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
FROM apsplandetail2 ap
LEFT JOIN containercurrentinfo cci ON cci.containerid = ap.containerid
LEFT JOIN Userattribute u ON u.parentid = cci.containerid AND u.userattributename = '热表时效性'
LEFT JOIN containerstepdispatch2 csd ON csd.containerid = cci.containerid AND csd.stepinfoid = ap.stepinfoid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN stepinfo s ON s.stepinfoid = csd.stepinfoid
LEFT JOIN resourcedef r ON r.resourceid = csd.resourceid
LEFT JOIN employeeresourcelist er ON er.resourcelistid = r.resourceid
LEFT JOIN employee e ON e.employeeid = er.employeeid
LEFT JOIN containersubcontainer csc ON csc.subcontainerid = cci.containerid
LEFT JOIN containercurrentinfo fac ON fac.containerid = csc.containerid 
LEFT JOIN containershiprecord csr ON csr.containerid = fac.containerid AND csr.isuse = 0 AND csr.toacceptdate IS NOT NULL
LEFT JOIN containershiprecordstep csrs ON csrs.containershiprecordid = csr.containershiprecordid 
LEFT JOIN workflowstep ws1 ON ws1.workflowstepid = csrs.workflowstepid 
WHERE ap.isuse = 0 AND ap.status = '1' AND cci.containerstatus = 1 AND cci.isinstore = 0 AND csd.isuse = 0 AND e.employeeid = '{0}' AND cci.originalfactoryid = '{1}'
AND (ws1.workflowstepid = csc.workflowstepid OR ws1.workflowstepname = csc.workflowstepname) --转工已接收
AND (fac.workflowstepname = csc.workflowstepname OR nvl(u.attributevalue,0) = 1) -- 主卡应该在当前转工序上，或者具有时效性
AND NOT EXISTS(SELECT 1 FROM containerstepworkreport2 csw WHERE csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0)--批量报工只能报没报工的
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = csd.containerid)
{2}
                     </string>.Value
        '无订单批次
        Dim strSelect2 = <string>
SELECT DISTINCT csd.containerstepdispatchid,cci.containername,cci.containerid,ws.workflowstepname,csd.workflowstepid,cci.workflowid,cci.productid,
s.stepinfoid,s.stepno,s.stepseq,s.description stepinfoname,s.revision stepinforev,csd.createdate,cci.qty wipqty,csd.qty disqty,cci.containertype,
DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
FROM apsplandetail2 ap
LEFT JOIN containercurrentinfo cci ON cci.containerid = ap.containerid
INNER JOIN Userattribute u ON u.parentid = cci.containerid AND u.userattributename = '委托批次'
LEFT JOIN containerstepdispatch2 csd ON csd.containerid = cci.containerid AND csd.stepinfoid = ap.stepinfoid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN stepinfo s ON s.stepinfoid = csd.stepinfoid
LEFT JOIN resourcedef r ON r.resourceid = csd.resourceid
LEFT JOIN employeeresourcelist er ON er.resourcelistid = r.resourceid
LEFT JOIN employee e ON e.employeeid = er.employeeid
WHERE ap.isuse = 0 AND ap.status = '1' AND cci.containerstatus = 1 AND cci.isinstore = 0 AND csd.isuse = 0 AND e.employeeid = '{0}' AND cci.originalfactoryid = '{1}'
AND NOT EXISTS(SELECT 1 FROM containerstepworkreport2 csw WHERE csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0)--批量报工只能报没报工的
AND NOT EXISTS(SELECT 1 FROM containercombinate cb WHERE cb.containerid = csd.containerid)
{2}
                     </string>.Value
        '合卡批次
        Dim strSelect3 = <string>
SELECT '' containerstepdispatchid, cb.containercombinatename containername,'' containerid,ws.workflowstepname,csd.workflowstepid,cci.workflowid,cci.productid,
s.stepinfoid,s.stepno,s.stepseq,s.description stepinfoname,s.revision stepinforev,max(csd.createdate) createdate,sum(cci.qty) wipqty,sum(csd.qty) disqty,cci.containertype,
DECODE(cci.CONTAINERTYPE,2,'批量加工',3,'批量带子序号', 7,'单件加工') AS CONTAINERTYPENAME
FROM apsplandetail2 ap
LEFT JOIN containercurrentinfo cci ON cci.containerid = ap.containerid
INNER JOIN containercombinate cb ON cb.containerid = cci.containerid
LEFT JOIN containerstepdispatch2 csd ON csd.containerid = cci.containerid AND csd.stepinfoid = ap.stepinfoid
LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
LEFT JOIN stepinfo s ON s.stepinfoid = csd.stepinfoid
LEFT JOIN resourcedef r ON r.resourceid = csd.resourceid
LEFT JOIN employeeresourcelist er ON er.resourcelistid = r.resourceid
LEFT JOIN employee e ON e.employeeid = er.employeeid
WHERE ap.isuse = 0 AND ap.status = '1' AND cci.containerstatus = 1 AND cci.isinstore = 0 AND csd.isuse = 0 AND e.employeeid = '{0}' AND cci.originalfactoryid = '{1}'
AND NOT EXISTS(SELECT 1 FROM containerstepworkreport2 csw WHERE csw.containerstepdispatchid = csd.containerstepdispatchid AND csw.isuse = 0)--批量报工只能报没报工的
GROUP BY ws.workflowstepname,csd.workflowstepid,cci.workflowid,cci.productid,cb.containercombinatename,
s.stepinfoid,s.stepno,s.stepseq,s.description,cci.containertype,s.revision
                     </string>.Value

        Dim strWhere = String.Empty '查询条件
        Dim strSelect = String.Format(<string>
                                          {0}
                                          UNION ALL
                                          {1}
                                          UNION ALL
                                          {2}
                                      </string>.Value, strSelect1, strSelect2, strSelect3)
        If para.Keys.Contains("CombinateName") Then
            If Not String.IsNullOrWhiteSpace(para("CombinateName")) Then
                strSelect = strSelect3 '如果存在合卡名查询，只查询合卡批次
                strWhere += " AND cb.containercombinatename like '%" + para("CombinateName") + "%'"
            End If
        End If
        '通用查询
        If Not String.IsNullOrWhiteSpace(para("OrderName")) Then
            strWhere += " AND cci.mfgordername like '%" + para("OrderName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ProductName")) Then
            strWhere += " AND cci.productname like '%" + para("ProductName") + "%'"
        End If

        If Not String.IsNullOrWhiteSpace(para("ContainerName")) Then
            strWhere += " AND cci.containername LIKE '%" + para("ContainerName") + "%'"
        End If

        strSelect = String.Format(strSelect, para("WorkerID"), para("FactoryID"), strWhere)

        strSelect = String.Format("select * from ({0})k order by k.containername,k.workflowstepid,k.stepseq", strSelect)

        Dim paraPage As New uMESPagingDataDTO
        paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
        paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
        paraPage.strSQL = strSelect
        Return DatabaseHelper.RunProcedureForPage(paraPage)
    End Function

    ''' <summary>
    ''' 工步报工前验证是否存在无效的派工 add by zhangrj 2023-04-20
    ''' </summary>
    ''' <param name="sqlStr"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsVaildDispatchForStepInfoWorkReport(ByVal sqlStr As String) As Boolean Implements uLMSWorkReportIBusiness.IsVaildDispatchForStepInfoWorkReport
        Dim strSql = "SELECT csd.isuse FROM containerstepdispatch2 csd WHERE " + sqlStr
        Dim dt = DatabaseHelper.Query_ReturnDatTable(strSql)
        If dt.Select("isuse = 1").Length > 0 Then
            Return False
        End If
        Return True
    End Function
#End Region

#Region "自检自分 add by zhangrj 20230607"
    ''' <summary>
    ''' 验证自检自分报工条件，专检序是否已经首件检验
    ''' </summary>
    ''' <param name="dt"></param>
    ''' <param name="isFirstCheck"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsValidSelfCheckReport(ByVal dt As DataTable, ByVal isFirstCheck As Boolean) As Boolean Implements uLMSWorkReportIBusiness.IsValidSelfCheckReport
        Dim res = True
        Dim strSql = <string>
                   SELECT 1
                   FROM containerstepdispatch csd 
                   LEFT JOIN  workflowstep ws ON ws.workflowstepid = csd.workflowstepid
                   LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                   LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                   WHERE csd.containerstepdispatchid = '{0}' AND csd.qty > 2
                   AND nvl(ws.controlcode,s.controlcode) IS NULL 
                     </string>.Value
        Dim strSql1 = <string>
                   SELECT 1 FROM containerstepworkreport csw 
                   WHERE csw.containerid = '{0}' AND csw.workflowstepid = '{1}' AND csw.isuse = 0 AND csw.checktype = 1
                     </string>.Value
        For Each dr As DataRow In dt.Rows
            If Not DatabaseHelper.GetSingle(String.Format(strSql, dr("containerstepdispatchid").ToString)) Is Nothing Then
                If isFirstCheck Then
                    If Not DatabaseHelper.GetSingle(String.Format(strSql1, dr("containerid").ToString, dr("workflowstepid").ToString)) Is Nothing Then
                        res = False
                        Exit For
                    End If
                Else
                    If DatabaseHelper.GetSingle(String.Format(strSql1, dr("containerid").ToString, dr("workflowstepid").ToString)) Is Nothing Then
                        res = False
                        Exit For
                    End If
                End If
            End If
        Next
        Return res
    End Function

    ''' <summary>
    ''' 保存自检自分报工数据
    ''' </summary>
    ''' <param name="para"></param>
    ''' <remarks></remarks>
    Public Sub SaveSelfCheckReportData(ByVal para As List(Of Dictionary(Of String, String)), ByVal m_UserInfo As CurrentUserInfo) Implements uLMSWorkReportIBusiness.SaveSelfCheckReportData
        Dim strSql = <string>
                        SELECT c.containerid,c.containername,c.qty wipqty,csw.qty,csw.workflowstepid,
                        c.workflowstepid nowworkflowstepid,csw.containerstepworkreportid,ws.sequence,c.workflowid
                        FROM containerstepworkreport csw 
                        LEFT JOIN containercurrentinfo c ON c.containerid = csw.containerid
                        LEFT JOIN  workflowstep ws ON ws.workflowstepid = csw.workflowstepid
                        LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid
                        LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
                        WHERE csw.containerstepworkreportid = '{0}' AND NVL(ws.controlcode,s.controlcode) = 'LY08'
                        AND nvl(csw.selfchecklossqty,0) = 0
                     </string>.Value
        Dim dtoList As New List(Of ContainerCheckInfoDTO) '自检的记录
        Dim moveDic As New List(Of Dictionary(Of String, String)) '需要移动的批次
        Dim SQLTransList As New List(Of Object())
        '处理报工数据
        For Each dic As Dictionary(Of String, String) In para
            Dim dt = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dic("ContainerStepWorkReportID")))
            '如果报工工序控制码是LY08（自动检验），且自检报废数为0，则自动检验
            If dt.Rows.Count > 0 Then
                Dim checkDto As New ContainerCheckInfoDTO
                checkDto.DefectQty = 0 '超差数
                checkDto.ReworkQty = 0 '返修数
                checkDto.LossQty = 0 '报废数
                checkDto.CheckQty = dt.Rows(0)("Qty").ToString '检验数量等于报工数量
                checkDto.WIPQty = dt.Rows(0)("WIPQTY").ToString '在制数
                checkDto.AcceptanceQty = dt.Rows(0)("Qty").ToString '合格数量等于报工数量
                checkDto.AcceptanceQty2 = dt.Rows(0)("Qty").ToString
                checkDto.ContainerName = dt.Rows(0)("ContainerName").ToString
                checkDto.ContainerID = dt.Rows(0)("ContainerID").ToString
                checkDto.WorkflowstepID = dt.Rows(0)("WorkflowstepID").ToString
                checkDto.FactoryID = m_UserInfo.FactoryID
                checkDto.CheckerID = m_UserInfo.EmployeeID
                checkDto.CheckNote = ""
                checkDto.ReportType = 2 '报工类型
                checkDto.ContainerStepWorkReportID = dic("ContainerStepWorkReportID").ToString
                checkDto.FirstCheckContainerNo = ""
                checkDto.FirstCheckBillNo = ""
                checkDto.SpecimenLossQty = 0 '需开废票试件数
                dtoList.Add(checkDto)
                SQLTransList.Add(m_CheckObj.FillContainerCheckInfo(checkDto))
                SQLTransList.Add(m_CheckObj.FillUpdateContainerCheckSuspending(checkDto.ContainerID, checkDto.WorkflowstepID))
                '如果报工的是当前序，则考虑移动到下一序
                If dt.Rows(0)("WorkflowstepID").ToString = dt.Rows(0)("NowWorkflowstepID").ToString Then
                    moveDic.Add(New Dictionary(Of String, String) From {{"ContainerID", dt.Rows(0)("ContainerID").ToString},
                                                                        {"WorkflowStepID", dt.Rows(0)("WorkflowstepID").ToString},
                                                                        {"WorkflowID", dt.Rows(0)("WorkflowID").ToString},
                                                                        {"Sequence", dt.Rows(0)("Sequence").ToString},
                                                                        {"ContainerName", dt.Rows(0)("ContainerName").ToString}
                                                                        })
                End If

            End If
        Next
        '执行检验存入
        If SQLTransList.Count > 0 Then
            If Not DatabaseHelper.ExecuteSqlTran(SQLTransList) Then
                Exit Sub
            Else
                For Each dto As ContainerCheckInfoDTO In dtoList
                    m_ConCurrentinfo.UpdateContainerCurrentinfoCheckInfo(dto.ContainerID, dto.WorkflowstepID)
                Next
            End If
        End If
        '获取检验数据
        strSql = <string>
                     SELECT cci.checkqty,cci.acceptanceqty,c.qty FROM containercheckinfo cci 
                     LEFT JOIN containercurrentinfo c ON c.containerid = cci.containerid
                     WHERE cci.containerid = '{0}' AND  cci.workflowstepid = '{1}' AND cci.isuse = 0
                 </string>
        '存在需要移动的批次
        If moveDic.Count > 0 Then
            Dim host As New ServerHostInfo
            host.UserName = m_UserInfo.APIUserName
            host.Password = m_UserInfo.APIPassword
            Dim levelAttributes As DataTable = m_CommonObj.GetFatoryAttributes(m_UserInfo.FactoryID)
            Dim strMessage = String.Empty
            For Each mdic As Dictionary(Of String, String) In moveDic
                Dim dtres = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, mdic("ContainerID"), mdic("WorkflowStepID")))
                '如果不存在，则不移动
                If dtres.Rows.Count = 0 Then
                    Continue For
                End If
                Dim checkqty = dtres.AsEnumerable().Sum(Function(p) p("checkqty")) '该序的检验数
                Dim acceptanceqty = dtres.AsEnumerable().Sum(Function(p) p("acceptanceqty")) '该序的合格数
                '如果该序的检验数量大于等于在制数，则移动下一序
                If checkqty >= Integer.Parse(dtres.Rows(0)("qty").ToString) Then
                    m_CheckObj.MoveNextStep(mdic, host, acceptanceqty, levelAttributes, m_UserInfo, strMessage)
                End If
            Next
        End If
    End Sub

#End Region

#Region "验证特殊过程工序（领工\报工时验证）add by zhangrj 20250331"

    ''' <summary>
    ''' 验证特殊过程工序（验证主方法，入口）add by zhangrj 20250331
    ''' </summary>
    ''' <param name="dtDatas">需要验证的数据源</param>
    ''' <param name="userInfo">操作人员</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ValidSpecialCourseSpecReportByReport(ByVal dtDatas As DataTable, ByVal userInfo As CurrentUserInfo) As SpecialReportValidDTO Implements uLMSWorkReportIBusiness.ValidSpecialCourseSpecReportByReport
        Dim reportDto As New SpecialReportValidDTO
        Dim strSql As String = <string>
                SELECT r.resourcename,c.containername,ws.workflowstepname,ws.workflowstepid
                FROM containerstepdispatch csd 
                LEFT JOIN container c ON c.containerid = csd.containerid
                LEFT JOIN resourcedef r ON r.resourceid = csd.resourceid
                LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                LEFT JOIN spec s ON s.specid=nvl(sb.revofrcdid,ws.specid)
                WHERE csd.containerstepdispatchid = '{0}' AND csd.isuse = 0 AND s.isspotcheck = 1
                AND NOT EXISTS(SELECT 1 FROM specialreportunlimitedinfo sr WHERE sr.containerid = csd.containerid AND sr.workflowstepid = csd.workflowstepid AND sr.status = 0) --未添加到无限制名单中                           
                     </string>.Value
        '循环每一个数据进行验证
        For Each dr As DataRow In dtDatas.Rows
            Dim dtSpecial As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dr("containerstepdispatchid").ToString()))
            '如果为空，则表示该工序不是特殊过程工序或者报工已撤销或者是无限制工序，则不验证
            If dtSpecial.Rows.Count = 0 Then
                Continue For
            End If
            Dim drRes = dtSpecial.Rows(0) '取第一条数据即可
            '开始验证该工序报工是否有效
            reportDto = IsValidSpecSpecialReport(drRes, userInfo)
            If Not reportDto.IsValid Then
                Exit For
            End If
        Next
        Return reportDto
    End Function

    ''' <summary>
    ''' 判断工序是否有效的特殊过程
    ''' </summary>
    ''' <param name="drSpec"></param>
    ''' <param name="userInfo"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Function IsValidSpecSpecialReport(ByVal drSpec As DataRow, ByVal userInfo As CurrentUserInfo) As SpecialReportValidDTO
        Dim reportDto As New SpecialReportValidDTO
        Dim strSql As String = String.Format(<string>
                SELECT ssr.indate,'' period,ssr.resourcenames,ssr.employeenames,ssr.specialcoursespecreportno
                FROM SpecialCourseSpecMapp scs 
                LEFT JOIN SpecialCourseSpecReport ssr ON ssr.specialcoursespecreportid = scs.specialcoursespecreportid
                WHERE scs.workflowstepid = '{0}'</string>.Value, drSpec("workflowstepid").ToString()) '查询SQL
        Dim dtReport As DataTable = DatabaseHelper.Query_ReturnDatTable(strSql) '报告表
        If dtReport.Rows.Count = 0 Then
            reportDto.IsValid = False
            reportDto.Message = String.Format("批次：{0}的工序：{1} 没有特殊过程报告，请联系工艺技术人员处理！", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
            Return reportDto
        End If
        '
        '****开始循环判断每一个报告的有效性(每一个报告都必须有效)
        '
        For Each drRe As DataRow In dtReport.Rows
            ''查询每个报告的数据，每个报告号可能是多个报告号连起来的（每一个都要验证）
            Dim nos As String() = drRe("specialcoursespecreportno").ToString().Split(",")
            strSql = <string>
                SELECT ssb.indate,ssb.period,ssc.resourcenames,ssc.employeenames
                FROM  specialcoursespecreportbank ssb
                LEFT JOIN specialcoursespeccontent ssc ON ssc.specialcoursespecreportbankid = ssb.specialcoursespecreportbankid
                {0}
                                   </string>.Value
            Dim dtNewReport As New DataTable '最终要验证的数据
            For Each no As String In nos
                Dim sqlWhere = String.Format("WHERE ssc.code LIKE '{0}%'", no) '查询条件
                Dim dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, sqlWhere)) '得到新的报告数据
                If dtRes.Rows.Count = 0 Then
                    sqlWhere = String.Format("WHERE ssb.specialcoursespecreportno LIKE '{0}%'", no) '查询条件
                    dtRes = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, sqlWhere)) '得到新的报告数据
                End If
                dtNewReport.Merge(dtRes)
            Next
            '
            '****开始做验证*********
            '
            If dtNewReport.Rows.Count > 0 Then '有新的报告数据，则验证该数据
                reportDto = IsValidSpecialReport(drSpec, dtNewReport, userInfo)
            Else
                '如果不存在新的报告数据，则验证自身即可
                Dim dtCopy = dtReport.Clone()
                dtCopy.Rows.Add(drRe.ItemArray)
                reportDto = IsValidSpecialReport(drSpec, dtCopy, userInfo)
            End If
            '如果无效了，则跳出外层循环
            If Not reportDto.IsValid Then
                Exit For
            End If
        Next
        Return reportDto
    End Function

    ''' <summary>
    ''' 判断该工序的该报告是否有效
    ''' </summary>
    ''' <param name="drSpec">工序</param>
    ''' <param name="dtReport">报告列表</param>
    ''' <param name="userInfo">当前用户</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsValidSpecialReport(ByVal drSpec As DataRow, ByVal dtReport As DataTable, ByVal userInfo As CurrentUserInfo) As SpecialReportValidDTO
        Dim reportDto As New SpecialReportValidDTO
        For Each drReport As DataRow In dtReport.Rows
            '判断是否没有有效期，如果两个表的有效期都是空，则提示
            If String.IsNullOrWhiteSpace(drReport("indate").ToString) Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 存在没有有效期的特殊过程报告，请联系工艺技术人员处理！", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
                reportDto.IsValid = False
                Exit For
            End If
            '-----获取有效期
            Dim inDate As Date = Date.Parse(drReport("indate").ToString) '有效期
            If Not String.IsNullOrWhiteSpace(drReport("period").ToString) Then '如果存在年数不为空，则要将有效期累加年数
                Dim period = 0 '年数
                Integer.TryParse(drReport("period").ToString.TrimEnd("年"), period)
                inDate = inDate.AddYears(period)
            End If
            '判断有效期是否过期
            If inDate < DateTime.Now Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 存在已过期的特殊过程报告，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
                reportDto.IsValid = False
                Exit For
            End If
            If inDate < Date.Now.AddDays(95) Then '如果有效期低于95天过期（需给出提示，验证通过）
                reportDto.IsUnder95Day = True
                reportDto.Message = String.Format("批次：{0}的工序：{1} 有效期已低于95天后过期，请知悉。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString())
            End If
            '判断是否包含检验人
            If Not drReport("employeenames").ToString.Contains(userInfo.EmployeeName.Substring(0, 8)) Then
                reportDto.Message = String.Format("批次：{0}的工序：{1} 的特殊过程报告未包含（编号为：{2}）的报工人员，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString(), userInfo.EmployeeName.Substring(0, 8))
                reportDto.IsValid = False
                Exit For
            End If
            ''判断是否包含设备
            'If Not drReport("resourcenames").ToString.Contains(drSpec("resourcename").ToString) Then
            '    reportDto.Message = String.Format("批次：{0}的工序：{1} 的特殊过程报告未包含（编号为：{2}）的设备，请联系工艺技术人员处理。", drSpec("containername").ToString(), drSpec("workflowstepname").ToString(), drSpec("resourcename").ToString)
            '    reportDto.IsValid = False
            '    Exit For
            'End If
        Next
        Return reportDto
    End Function

#End Region

#Region "验证三定卡片工序 add by zhangrj 20251011"
    ''' <summary>
    ''' 验证三定卡片工序（验证主方法，入口）add by zhangrj 20251011
    ''' </summary>
    ''' <param name="dtDatas">需要验证的数据源</param>
    ''' <param name="userInfo">操作人员</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ValidSpecThreeFixedCardByReport(ByVal dtDatas As DataTable, ByVal userInfo As CurrentUserInfo) As Tuple(Of Boolean, String) Implements uLMSWorkReportIBusiness.ValidSpecThreeFixedCardByReport
        Dim res As New Tuple(Of Boolean, String)(True, "")
        Dim strSql As String = <string>      
                SELECT ws.workflowstepname,tcd.code,tcd.name
                FROM containerstepdispatch csd
                LEFT JOIN workflowstep ws ON ws.workflowstepid = csd.workflowstepid
                LEFT JOIN Workflow w ON w.workflowid = ws.workflowid
                LEFT JOIN workflowbase wb ON wb.workflowbaseid = w.workflowbaseid
                LEFT JOIN specbase sb ON sb.specbaseid=ws.specbaseid
                LEFT JOIN spec s ON s.specid=nvl(sb.revofrcdid,ws.specid)
                INNER JOIN ThreeFixedCards tc ON tc.workflowname = wb.workflowname AND tc.workflowversion = w.workflowrevision AND tc.specno = s.specno
                LEFT JOIN ThreeFixedCardDetail tcd ON tcd.parentid = tc.id AND tcd.threetype = '0' --只筛选工人
                WHERE csd.containerstepdispatchid = '{0}' AND csd.isuse = 0
                     </string>.Value
        '循环每一个数据进行验证
        For Each dr As DataRow In dtDatas.Rows
            Dim dtThreeCard As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(strSql, dr("containerstepdispatchid").ToString()))
            '如果为空，则表示该工序不是三定卡片，则不验证
            If dtThreeCard.Rows.Count = 0 Then
                Continue For
            End If
            If Not IsValidSpecThreeFixedCard(dtThreeCard, userInfo.EmployeeName) Then
                res = New Tuple(Of Boolean, String)(False, String.Format("工序：{0}是三定卡片，未包含编号为：{1}的报工人，无法报工！", dtThreeCard.Rows(0)("workflowstepname").ToString(), userInfo.EmployeeName))
                Exit For
            End If
        Next
        Return res
    End Function

    ''' <summary>
    ''' 验证三定数据是否包含该报工人
    ''' </summary>
    ''' <param name="dtThreeCard"></param>
    ''' <param name="employeeName"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function IsValidSpecThreeFixedCard(ByVal dtThreeCard As DataTable, ByVal employeeName As String) As Boolean
        Dim res = False
        '循环每一个数据进行验证
        For Each dr As DataRow In dtThreeCard.Rows
            If dr("code").ToString() = employeeName Then
                res = True
                Exit For
            End If
        Next
        Return res
    End Function
#End Region

#Region "验证领工工序排产时间是否超过两天 add by zhangrj 20251111"
    ''' <summary>
    ''' 领工时验证工序的排产是否在未来两天内的计划时间
    ''' </summary>
    ''' <param name="dtDatas"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function IsApsOverTimeByReport(ByVal dtDatas As DataTable) As Tuple(Of Boolean, String) Implements uLMSWorkReportIBusiness.IsApsOverTimeByReport
        Dim res As New Tuple(Of Boolean, String)(False, "")
        Dim sql = <string>
                        SELECT c.containername,ap.workflowstepname,ap.starttime FROM containerstepdispatch csd 
                        LEFT JOIN containercurrentinfo c ON c.containerid = csd.containerid
                        LEFT JOIN apsplaninfo ai ON ai.containerid = csd.containerid
                        LEFT JOIN apsplandetail ap ON ap.apsplaninfoid = ai.apsplaninfoid AND ap.workflowstepid = csd.workflowstepid
                        WHERE csd.containerstepdispatchid = '{0}' AND ai.apstype2 = '2'
                  </string>.Value
        For Each dr As DataRow In dtDatas.Rows
            Dim dtAps As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(sql, dr("containerstepdispatchid").ToString()))
            '如果排产为空，则跳过验证
            If dtAps.Rows.Count = 0 Then
                Continue For
            End If
            Dim startTime As New Date '计划开始时间
            If Date.TryParse(dtAps.Rows(0)("starttime").ToString(), startTime) Then '时间没问题，则验证
                If startTime.Date > Date.Now.AddDays(2).Date Then '如果计划开始时间大于两天后，则超期
                    res = New Tuple(Of Boolean, String)(True, String.Format("批次：{0}的工序：{1}的计划开始时间为：{2}，不能提前两天领工！", dtAps.Rows(0)("containername").ToString(), dtAps.Rows(0)("workflowstepname").ToString(), dtAps.Rows(0)("starttime").ToString()))
                    Exit For
                End If
            End If
        Next
        Return res
    End Function
#End Region
End Class
